This discussion is archived
3 Replies Latest reply: Sep 18, 2013 11:19 AM by Epic Fail RSS

Elegant Handeling Of raise_application_error In APEX

Epic Fail Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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

  • 2. Re: Elegant Handeling Of raise_application_error In APEX
    Mike Kutz Expert
    Currently Being Moderated

    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
  • 3. Re: Elegant Handeling Of raise_application_error In APEX
    Epic Fail Newbie
    Currently Being Moderated

    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.


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