6 Replies Latest reply: Apr 25, 2013 1:43 PM by 1002371 RSS

    Field not updating when switching fields.

    1002371
      I am using Oracle Forms Builder 10g and I created a procedure to get the data from a related table into the data block
      .
      PROCEDURE GET_MODEL_VAL IS
      CURSOR MODEL_VAL IS
      SELECT VMODEL_NAME
      FROM VEHICLE, VEHICLE_MODEL
      WHERE :VEHICLE.VEHICLE_ID = VEHICLE.VEHICLE_ID
      AND :VEHICLE.VMODEL_ID = VEHICLE_MODEL.VMODEL_ID;
      BEGIN
        OPEN MODEL_VAL;
        FETCH MODEL_VAL INTO
        :VEHICLE.VMODEL_NAME;
        IF MODEL_VAL %NOTFOUND THEN
             :VEHICLE.VMODEL_NAME := NULL;
        END IF;
        CLOSE MODEL_VAL;
        EXCEPTION
             WHEN NO_DATA_FOUND THEN
             MESSAGE ('Model Data Not Found');
             WHEN OTHERS THEN
             MESSAGE ('Post-Query Unsuccessful');
      END;
      And I created an on validate trigger to check if the FK is valid and then call the above pcedure
      DECLARE
           NUM NUMBER;
      BEGIN
           SELECT COUNT(*) INTO NUM
           FROM VEHICLE, VEHICLE_MODEL
           WHERE :VEHICLE.VMODEL_ID =  VEHICLE_MODEL.VMODEL_ID;
           IF
                NUM = 0 THEN
                DISPLAY_VMID_ALERT;
                RAISE FORM_TRIGGER_FAILURE;
           END IF;
           GET_MODEL_VAL;
      END;
      Now here's the fun part. If I am updating the record when I tab or click away from the VMODEL_ID field the VMODEL_NAME field updates and everything is fine, however during an insert it does not.

      Any insight?

      The Oracle Peon...

      Edited by: Oracle Peon on Apr 25, 2013 10:18 AM

      Edited by: Oracle Peon on Apr 25, 2013 10:31 AM

      Edited by: Oracle Peon on Apr 25, 2013 11:25 AM
        • 1. Re: Field not updating when switching fields.
          user346369
          Please explain what you mean by "during an insert it does not".

          Are you saying you enter a value and then press some button to start an insert process? How does the insert run?

          By the way:
          1. Open-Fetch-Close never raises no_data_found exceptions, so the when no_data_found part is wasted space.
          2. When Others: Better to display SQLERRM. At least you then get the Oracle error message that caused the error.
          3. When you post code, place it between [code] and [/code] tags.

          Edited by: Steve Cosner on Apr 25, 2013 10:50 AM
          Changed "CODE" to "code"
          • 2. Re: Field not updating when switching fields.
            Amatu Allah Neveen Ebrahim
            Hi
            First
            open cursor loop ... Fetch into ... End loop... Close cursor
            put the check for the fk in where clause of the cursor remove second validation then call ur procedure generate & try again.

            Amatu Allah
            • 3. Re: Field not updating when switching fields.
              Andreas Weiden
              Now here's the fun part.
              Well, it would have been fun if you had posted that updating stuff, because there seems to be the error..
              • 4. Re: Field not updating when switching fields.
                1002371
                I was just typing up a response to you Steve when it hit me, the VEHICLE_ID isn't in the database yet during an insert.
                WHERE :VEHICLE.VEHICLE_ID = VEHICLE.VEHICLE_ID
                AND :VEHICLE.VMODEL_ID = VEHICLE_MODEL.VMODEL_ID;
                should be
                WHERE :VEHICLE.VMODEL_ID = VEHICLE_MODEL.VMODEL_ID;
                Now I'm a bit new to Oracle so excuse my ignorance but you and Amatu lost me in your explanation.
                I'm going to mark this as answered but I'd appreciate either a little more detail on what you guys did answer or some links to help me learn a bit more.

                Thanks...
                The Oracle Peon...

                Edited by: Oracle Peon on Apr 25, 2013 11:25 AM
                • 5. Re: Field not updating when switching fields.
                  user346369
                  OP: I just realized, when you use code tags, they need to be lower case. (Go back and edit your posts, and see what happens.)
                  Now I'm a bit new to Oracle so excuse my ignorance but you and Amatu lost me in your explanation.
                  I'm going to mark this as answered but I'd appreciate either a little more detail on what you guys did
                  answer or some links to help me learn a bit more.
                  I think everyone was confused due to the limited explanation in your first post.