3 Replies Latest reply on Feb 14, 2001 1:09 AM by 3004

    Displaying a trigger error in forms

    3004
      In a before inserting table trigger, if inserted values are not correct, I raise an error :
      RAISE_APPLICATION_ERROR(-20001,'Error message !');

      What I want is to display this error message in forms instead of the error 40508 - ORACLE error: unable to INSERT record.

      How can I ?
        • 1. Displaying a trigger error in forms
          3004
          Use the ON-ERROR trigger.

          If error_code = 40508 then
          message('your message here', acknowledge);
          End if;
          • 2. Displaying a trigger error in forms
            3004
            1. What I want is to display the message of the trigger, not to duplicate it in forms (if I change the message in my trigger, I want the message in forms to change too).
            2. Your solution is not perfect because if the insertion is impossible due to something else (unique constraint, null value, ...), I would nevertheless receive the message.
            • 3. Displaying a trigger error in forms
              3004
              look at Dbms_Error_Code and Dbms_Error_Text.

              You'll have to parse them, but you can grab the error messages off them.

              Here's a similar question I asked on Metalink... I never received an error there, but the solution was to write an on-error trigger for the block and capture the form errors related to inserts, updates, and deletes. Here's the question though:


              Oracle Forms/Web Forms Technical Forum




              From: ERIC GIVLER 18-Nov-00 15:16
              Subject: "Clean" capture of DBMS Error messages on raise_application_error


              "Clean" capture of DBMS Error messages on raise_application_error

              This is with Forms 4.5 Developer 1.3.2 (32bit)

              Has anyone written forms level triggers, I guess ON-ERROR triggers to properly capture the error messages raised from a database trigger or procedure that uses a RAISE_APPLICATION_ERROR?

              I'd like to display the SAME message that I'm passing to RAISE application error, the message only, without all the other garbage.

              I was thinking of capturing the dbms_error_code and dbms_error_text, and then based on the form error code - kind of like in the example code in the Forms Help. Then, I'd parse these strings and strip off my error message, because the dbms_error_text contains the entire "error stack", ie:

              ORA-20100: SEASON DATES ERROR! Reservations exist in this date range
              ORA-06512: at "SUNTRACK.SEASON_DATES_BR_D", line 19
              ORA-04088: error during execution of trigger 'SUNTRACK.SEASON_DATES_BR_D'

              So... I'd like to just get "SEASON DATES ERROR! Reservations exist in this date range" message

              Questions:
              1. Is there an easy way to do this that I'm missing, or do I have to brute force, look for 'ORA'| |dbms_error_code| |': ' in my dbms_error_text, strip that off the front, and then grab the error text up to the first LINEFEED, chr(10), found in the dbms_error_text - that just seems a little "kludgy"

              2. Anyone have a nice solution? IT seems like there should be a "standard" on-error trigger that handles this type of situation.

              3. What would be all the form errors that I should look for that could have been the result of an error raised in a trigger???
              ie. frm-40509 (unable to update), frm-40510 (unable to delete

              null