Earlier versions of Oracle offered some PL/SQL trace capabilities, but Oracle8i Database introduced an API that allows you to easily specify and control the tracing of the execution of PL/SQL procedures, functions, and exceptions. The DBMS_TRACE built-in package provides programs to start and stop PL/SQL tracing in a session. When tracing is turned on, the engine collects data as the program executes. The data is then written out to the Oracle Server trace file.
In addition to DBMS_TRACE, you can take advantage of the built-in function, DBMS_UTILITY.FORMAT_CALL_STACK, to obtain the execution call stack at any point within your application.
This function returns the execution call stack (the sequence of program calls) down to the point at which you call the function. Here is an example of the formatting of this stack string:
----- PL/SQL Call Stack ------
88ce3f74 8 package
One of the best places to use this function is within an exception handler, as in:
Better yet, grab this information and write it to your log table, so that the support and debug teams can immediately see how you got to the point where the problem reared its ugly head.
There is, by the way, one big problem with the exception section above: if your call stack is deep, the formatted string will exceed 255 characters in length. Before Oracle Database 10g Release 2, DBMS_OUTPUT.PUT_LINE would raise an exception in such cases. To avoid this problem, you might consider using Darko Egersdorfer’s callstack package, found in the callstack.pkg file on the book’s web site.
This package may not have been installed automatically with the rest of the built-in packages. To determine whether DBMS_TRACE is present, connect to SYS (or another account with SYSDBA privileges) and execute this command:
BEGIN DBMS_TRACE.CLEAR_PLSQL_TRACE; END;
If you see this error:
PLS-00201: identifier 'DBMS_TRACE.CLEAR_PLSQL_TRACE' must be declared
then you must install the package. To do this, remain connected as SYS (or another account with SYSDBA privileges), and run the following files in the order specified:
The following programs are available in the DBMS_TRACE package:
To trace execution of your PL/SQL code, you must first start the trace with a call to:
DBMS_TRACE.SET_PLSQL_TRACE (trace_level INTEGER);
in your current session, where trace_level is one of the following values:
To turn on tracing from all programs executed in your session, issue this call:
To turn on tracing for all exceptions raised during the session, issue this call:
You then run your code. When you are done, you stop the trace session by calling:
You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will usually look at the modification dates to figure out which file to examine. The location of the trace files is discussed in the later section, “Format of collected data.”
Note that you cannot use PL/SQL tracing with the shared server (formerly known as the multithreaded server, or MTS).
blog comments powered by Disqus