PL/SQL tracing for swallowed exceptions

-- 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;

/