Who caught my error ?

This one is prompted by a Stackoverflow question here

DBMS_TRACE is a handy way to peek under the scenes in PL/SQL. The bad news is you'll need someone with SYSDBA privileges to install the required tables. I suggest you buy your DBA a box of chocolates for Christmas, and then suggest that you would be annoying them less often if they'd install the objects. Oh, and while they are there, could they give you some select privileges on some of the V$ views.

The required tables are in a script called tracetab.sql in $ORACLE_HOME/rdbms/admin. They get installed under SYS and you'll want some privileges to manage them too.

D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 14 09:24:45 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> @tracetab.sql
SQL> CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
SQL> CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
SQL> CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
SQL> GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;


Now for a demo :

create or replace
procedure test_trace is
v_test varchar2(3);
begin
select '12' into v_test from dual;
select '123' into v_test from dual;
select '1234' into v_test from dual;
select '12345' into v_test from dual;
exception
when value_error then
null;
end;
/

Somewhere in the mire of code, an exception is being raised. It could be a select or other SQL statement or just a PL/SQL assignment. The benefit of DBMS_TRACE over an SQL trace is that it catches PL/SQL exceptions.

Run the test by clearing out the table of any old junk, set the flag and execute the procedure

conn .../...@xe
delete from plsql_trace_events;
commit;
exec DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
exec test_trace;

Then query the results.

conn .../...@xe
select event_kind, event_unit, event_line, stack_depth, excp, event_comment, callstack, errorstack
from plsql_trace_events
where event_kind not in (38,40,43,44)
order by event_seq;

EVENT_KIND EVENT_UNIT EVENT_LINE STACK_DEPTH EXCP
----------- ------------------------------- ----------- ----------- -----------
EVENT_COMMENT
----------------------------------------------------------------------------------
CALLSTACK
----------------------------------------------------------------------------------
ERRORSTACK
----------------------------------------------------------------------------------
52.00 TEST_TRACE 6.00 2.00 6,502.00
Exception raised
----- PL/SQL Call Stack -----
object line object
handle number name
3BF0F6D4 6 procedure GARY.TEST_TRACE
3BDF1764 1 anonymous block
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

53.00 TEST_TRACE 11.00 2.00 6,502.00
Exception handled

We can see an exception was raised at line 6 and the fact that it was caught at line 11. The latter is pretty important too. If you have some complex code, it isn't impossible that an exception handler a couple of levels up the call hierarchy may 'handle' an exception for which it was never intended. You can even see the error number which is handy if it got caught by a WHEN OTHERS.