This discussion is archived
6 Replies Latest reply: Apr 25, 2013 11:43 AM by 1002371 RSS

Field not updating when switching fields.

1002371 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.
  • 6. Re: Field not updating when switching fields.
    1002371 Newbie
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points