I wrote this trigger around 4 years ago to handle errors in an application based on Oracle Forms 6i. This trigger handles all errors with some custom messages for some specific errors and not only this after giving an appropriate message to the user it logs the error into a table named error_log so that a DBA can view all the errors with their execution time, user and program information. See the example below:
On-Error Trigger code:
declare vabutton number; verrtxt varchar2(80) := error_text; verrno number := error_code; vdbms number := dbms_error_code; verrtype varchar2(20) := error_type; begin if vdbms = -3114 or vdbms = -1017 or vdbms = -3115 or vdbms = -1012 then -- logon related errors set_alert_property('errmes', title, 'App '||ltrim(to_char(vdbms))); set_alert_property('errmes', alert_message_text, 'Logon denied.'); vabutton := show_alert('errmes'); raise form_trigger_failure; end if; if verrno = 41009 OR VERRNO = 41008 or verrno = 40100 OR VERRNO = 40105 then --- ignoring all errors like at first record etc. NULL; elsif verrno = 40509 then insert into error_log (sqno, username, error_msg, error_cd, error_tp, error_dt, LOCATION) values (error_seq.nextval, :MAIN.USERNAME, verrtxt, verrno, verrtype, sysdate, :SYSTEM.CURSOR_BLOCK); frmsave; set_alert_property('errmes', title, 'Info.'||ltrim(to_char(verrno))); set_alert_property('errmes', alert_message_text, 'You cannot update records.'); vabutton := show_alert('errmes'); :main.er := :main.er + 1; else insert into hms.error_log (sqno, username, error_msg, error_cd, error_tp, error_dt, LOCATION) values (hms.error_seq.nextval, :MAIN.USERNAME, verrtxt, verrno, verrtype, sysdate, :SYSTEM.CURSOR_BLOCK); --- frmsave is the database procedure to commit explicitly. frmsave; set_alert_property('errmes', title, 'Info.'||ltrim(to_char(verrno))); set_alert_property('errmes', alert_message_text, verrtxt); vabutton := show_alert('errmes'); :main.er := :main.er + 1; end if; exception when form_trigger_failure then null; when others then -- FOR DEBUG NEXT LINE TO KNOW ERROR NUMBER -- set_alert_property('errmes', alert_message_text, '['||TO_CHAR(ERROR_CODE)||'] '||error_text); insert into error_log (sqno, username, error_msg, error_cd, error_tp, error_dt, LOCATION)values (error_seq.nextval, :MAIN.USERNAME, verrtxt, verrno, verrtype, sysdate, :SYSTEM.CURSOR_BLOCK); frmsave; set_alert_property('errmes', alert_message_text, error_text); vabutton := show_alert('errmes'); :main.er := :main.er + 1; end;
Error_Log Table structure:
1 | SQNO | NUMBER(10) |
2 | USERNAME | VARCHAR2(20 BYTE) |
3 | ERROR_MSG | VARCHAR2(200 BYTE) |
4 | ERROR_CD | NUMBER(10) |
5 | ERROR_TP | VARCHAR2(10 BYTE) |
6 | ERROR_DT | DATE |
7 | LOCATION | VARCHAR2(20 BYTE) |
See also: Writing On-Error Trigger in Oracle Forms