3 Replies Latest reply on Sep 18, 2013 6:19 PM by Epic Fail

    Elegant Handeling Of raise_application_error In APEX

    Epic Fail

      I have triggers that allow my views to be used for inserts or updates.  I have built exceptions into them that catch errors caused by the user.  I am looking for a way to display the error messages in a user friendly way.  The triggers/views are being used in a data load wizard in APEX 4.2.2.


      Example: for any stored procedure, function or trigger a user uploads data that is not acceptable

      main_lbl_format exception; -- is declared


      raise main_lbl_format;  -- is called when error is detected



           when main_lbl_format then

             raise_application_error(-20001,'ERROR IN MAIN_LABEL',TRUE ); --exception is handled by raising application error


        results in this being displayed:

        ORA-20001: ERROR IN MAIN_LABEL ORA-06512: at "TRIGGER_NAME", line 33 ORA-06510: PL/SQL: unhandled user-defined exception ORA-04088: error during execution of trigger 'TRIGGER_NAME'


      It would be nice to just show 'ERROR IN MAIN_LABEL' instead of everything else.


      Because of how APEX data load wizards work apex validations before the trigger has a chance to run is not possible.  APEX also does not recognize using DBMS_OUTPUT.PUT_LINE instead of  raise_application_error as an error.


      This is a somewhat vague question but any thoughts/ideas you have are welcome.

        • 1. Re: Elegant Handeling Of raise_application_error In APEX
          Tom Petrus

          Take a look at a custom error handling function, described in the apex_error api documentation: APEX_ERROR

          It is based on an example created by Patrick Wolf and is also on his blog:

          APEX 4.1- Error Handling Improvements- Part 1 | Inside Oracle APEX by Patrick Wolf

          APEX 4.1 - Error Handling Improvements - Part 2 | Inside Oracle APEX by Patrick Wolf

          1 person found this helpful
          • 2. Re: Elegant Handeling Of raise_application_error In APEX
            Mike Kutz

            two parts


            part 1:  APEX handles custom errors

            I'm pretty sure that APEX 4.? can catch certain errors and change what is displayed in the Error Message.

            I believe it was listed as one of the 'new features' for 4.(i forgot).

            Start there for your documentation research.


            part 2: code enhancement suggestions

            You can initialize the exception with a string. (didn't see if you did)

            Also, I wouldn't use the exception clause unless you will be logging the error.


            create or replace
            trigger ...
              main_lbl_format exception;
              pragma exception_init( -20001, 'ERROR IN MAIN_LABEL' );
            -- blah
            if not_good then
              raise main_lbl_format;
            end if;
               when main_lbl_format then
                  log_error( .... ); -- this procedure needs to be PRAGMA AUTONOMOUS _TRANSACTION
            1 person found this helpful
            • 3. Re: Elegant Handeling Of raise_application_error In APEX
              Epic Fail

              Both Tom's and Mike's responses are good but I think my issue is with the data load wizard.  It doesn't display errors in the message region like the rest of APEX.  Instead it just puts them into a results table.  The forum will not let me attach an image.  The table is generated by a collection generated by the wizard


              select n001 as row_num,

                     c049 as action,

                     c048 as error,

                     c001, c002, c003,

                     c004, c005, c006,

                     c007, c008, c009,

                     c010, c011, c012,

                     c013, c014, c015,

                     c016, c017, c018,

                     c019, c020, c021,

                     c022, c023, c024,

                     c025, c026, c027,

                     c028, c029, c030,

                     c031, c032, c033,

                     c034, c035, c036,

                     c037, c038, c040,

                     c041, c042, c043,

                     c044, c045

                from apex_collections

              where collection_name = 'LOAD_CONTENT'

                 and c047 in ('FAILED')

              order by seq_id


              I guess I could do a case statement on the c048 column and look for 'ORA-20XXX' and replace it with appropriate message.  I was hoping there was a way to do this without doing any serious modification to the wizard.


              I do log all the errors generated in my apps.  We are a small company (<100) users and my users are not to good at reporting issues.  They tend to just find work a rounds instead of picking up the phone and reporting a problem.  So I watch for recurring errors and when I see a trend I go proactive/reactive to solve the problem. 


              Just as an aside is proactive or reactive the correct term to use in the last sentence? I am proactive b/c I respond before a user says anything.  On the other hand I am reactive b/c the problem/errors have already occurred.