How to set it up

This is actually well documented here:

http://www.tonyex.net/TuneWiki/doku.php?id=plsql:dbms_trace

It basically involves preparing Oracle for tracing by creating the necessary trace tables, and then executing a SQL trace command in the beginning of the stored procedure that you are tracing; something like:

execute immediate 'alter session set EVENTS=''10938 TRACE NAME CONTEXT LEVEL 5''';

When I tried this on a v10.2.0.3 database, the result was not a trace file (as the article above states); instead, there were rows filled into the sys.plsql_trace_runs and sys.plsql_trace_events tables. There’s one entry for each execution in the plsql_trace_runs table, and one row for every line executed in the plsql_trace_events table.

What really helped was running this query into an HTML spoolfile:

set markup html on
spool test.html

SELECT DECODE(PTE.event_unit_owner, NULL, NULL, PTE.event_unit_owner ||'.' || PTE.event_unit) event_unit,
    PTE.event_line, PTE.stack_depth, PTE.event_comment, ASRC.text, PTE.event_seq,
    DECODE(PTE.proc_owner, NULL, NULL, PTE.proc_owner || '.') || PTE.proc_unit proc_unit, PTE.proc_line,
    PTE.event_time, PTE.event_unit_kind , excp,errorstack
FROM PLSQL_TRACE_EVENTS PTE, ALL_SOURCE ASRC
WHERE REPLACE(PTE.event_unit_kind, ' SPEC', '') = ASRC.TYPE (+)
    AND PTE.event_unit_owner = ASRC.owner (+)
    AND PTE.event_unit = ASRC.name (+)
    AND PTE.event_line = ASRC.line (+)
    AND PTE.runid = 12
    AND PTE.event_unit_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY PTE.event_seq

spool off
set markup html off

The ‘errorstack’ column is a BLOB, and it will actually contain the error itself, when there is a line executed that has an EVEN_COMMENT = ‘Exception raised’.

You can basically just step through the results of the query above, to figure out what’s really happening. Find the ‘Exception Raised’ lines, and see what’s happening before then!

A few caevets:

1. You need to add the trace call above into your existing PL/SQL package/procedure/function.
2. You need to recompile the PL/SQL that you plan to trace using the ‘debug’ mode, as in: alter package xxx compile debug; alter package xxx compile debug body;
3. Note that if this is a heavily-used procedure that you’re testing, then the plsql_trace_run and plsql_trace_events tables may get very very large very quickly, so you probably want to enable that tracing a very short amount of time.

Happy tracing!