-- Bad PL/SQL, swallowing exceptions
CREATE OR REPLACE PROCEDURE test1 AS
i NUMBER;
BEGIN
BEGIN
BEGIN
i := 1/0;
EXCEPTION
WHEN OTHERS THEN
-- Divide by zero exception will be caught here
raise_application_error(-20001, 'Custom message 1');
END;
EXCEPTION
WHEN OTHERS THEN
-- Custom exception 1 will be caught here
raise_application_error(-20002, 'Custom message 2');
END;
EXCEPTION
WHEN OTHERS THEN
-- Custom exception 2 will be caught here
raise_application_error(-20003, 'Custom message 3');
END;
/
-- Pre-requisites as sys user for tracing
@?/rdbms/admin/tracetab.sql
-- Starting tracing for exceptions
exec dbms_trace.set_plsql_trace(dbms_trace.trace_all_exceptions);
-- Get run id of current session
select dbms_trace.get_plsql_trace_runnumber from dual;
-- Now execute PL/SQL code
-- Stopping tracing
exec dbms_trace.clear_plsql_trace;
-- Get tracing of PL/SQL (as a user with select dictionary privilege)
select event_unit,
event_line,
errorstack
from sys.plsql_trace_events
where runid = 4
order by event_seq;
-- Sample output
-- EVENT_UNIT EVENT_LINE ERRORSTACK
-- --------------- ---------- ----------------------------------------
-- <anonymous> 0
-- <anonymous> 0
-- TEST1 6 ORA-01476: divisor is equal to zero
-- TEST1 10 ORA-01476: divisor is equal to zero
-- TEST1 10 ORA-20001: Custom message 1
-- TEST1 15 ORA-20001: Custom message 1
-- TEST1 15 ORA-20002: Custom message 2
-- TEST1 20 ORA-20002: Custom message 2
-- TEST1 20 ORA-20003: Custom message 3
-- <anonymous> 1
-- Purge tracing table
delete from sys.plsql_trace_events;
-- Good PL/SQL, prepend last exception before raising
CREATE OR REPLACE PROCEDURE test1 AS
i NUMBER;
BEGIN
BEGIN
BEGIN
i := 1/0;
EXCEPTION
WHEN OTHERS THEN
-- Divide by zero exception will be caught here
raise_application_error(-20001, DBMS_UTILITY.FORMAT_ERROR_STACK ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
'Custom message 1');
END;
EXCEPTION
WHEN OTHERS THEN
-- Custom exception 1 will be caught here
raise_application_error(-20002, DBMS_UTILITY.FORMAT_ERROR_STACK ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
'Custom message 2');
END;
EXCEPTION
WHEN OTHERS THEN
-- Custom exception 2 will be caught here
raise_application_error(-20003, DBMS_UTILITY.FORMAT_ERROR_STACK ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
'Custom message 3');
END;
/
