Tracing PL/SQL executions
Here’s a hypothetical problem: you are developing a PL/SQL application, that is in production. Unfortunately, in production the code is failing somehow, and you can’t figure out where or why. You’re unable to reproduce the problem in your development environment. The production server is behind lots of VPNs and firewalls, so you can’t easily get to it except through a terminal (ssh) session. You do have a rough idea of the package that is most likely causing the problem.
What to do?
What you need to figure out is this; where is it failing, and what’s the code path that it took to get there? One answer lies with the
How to set it up
This is actually well documented here:
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_events tables. There’s one entry for each execution in the
plsql_trace_runs table, and one row for every line executed in the
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:
- You need to add the trace call above into your existing PL/SQL package/procedure/function.
- 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;
- Note that if this is a heavily-used procedure that you’re testing, then the
plsql_trace_eventstables may get very very large very quickly, so you probably want to enable that tracing a very short amount of time.