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.
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:
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 ... is main_lbl_format exception; pragma exception_init( -20001, 'ERROR IN MAIN_LABEL' ); begin -- blah if not_good then raise main_lbl_format; end if; exception when main_lbl_format then log_error( .... ); -- this procedure needs to be PRAGMA AUTONOMOUS _TRANSACTION raise; end; /
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,
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.