26.1.1 Enabling Program Units for Tracing

In order to trace the execution of a program, you will first have to enable that program by recompiling it with the debug option. You can do this either by altering your session and then issuing a CREATE OR REPLACE statement, or by directly recompiling an existing program unit with the debug option.

To alter your session to turn on PL/SQL debug mode for compilation, issue this command:

SQL> ALTER SESSION SET PLSQL_DEBUG=TRUE;

Then compile your program unit with a CREATE OR REPLACE statement. That program unit will then be available for tracing.

You can also recompile your existing, stored program with debug mode as follows:

SQL> ALTER [PROCEDURE|FUNCTION|PACKAGE] <program_name> COMPILE DEBUG;

So if you wanted to enable the emp_pkg package for tracing, you would issue this command:

SQL> ALTER PACKAGE emp_pkg COMPILE DEBUG;

From within a PL/SQL program you can also turn on debug mode for a module by using DBMS_SQL as follows:

CREATE OR REPLACE PROCEDURE debugpkg (name IN VARCHAR2)
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   fdbk INTEGER;
BEGIN
   DBMS_SQL.PARSE (cur,
      'ALTER PACKAGE ' || name || ' COMPILE DEBUG',
      DBMS_SQL.NATIVE);

   fdbk := DBMS_SQL.EXECUTE (cur);

   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

For more information on DBMS_SQL, see Appendix C, Built-In Packages.

26.1.2 Turning On the Trace

Once you have enabled the desired program units for tracing, you can execute those programs (or the application code which makes use of those programs). To get trace output, however, you must turn on tracing for your session. You can request tracing of program calls and/or exceptions raised in programs.

You do this with the ALTER SESSION command as follows:

   SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL <number>';

where 10938 is the event number for PL/SQL tracing and <number> is a number indicating the level of tracing you desire. Valid tracing levels are:

You can activate multiple event levels for tracing by adding the level values. For example, the following statement sets tracing for levels 2 and 8:

SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL 10';

while this next command activates tracing for levels 2, 4, and 8:

SQL> ALTER SESSION SET EVENTS='10938 TRACE NAME CONTEXT LEVEL 14';

Lower trace levels supersede higher levels. So if you activate tracing for level combination 12, level 8 will be ignored and trace output will be produced for all exceptions, not just exceptions in enabled program units.

As you can see, you have some control over the granularity of tracing. It is not possible, however, to activate tracing just within a specific program. It is either all programs or all programs in which tracing has been enabled with a debug-mode compile.

NOTE: You cannot turn on tracing for remote procedure calls (RPCs) -- that is, programs which are stored in remote databases.

26.1.3 A Sample Tracing Session

To make it easier for me to test and use this facility I created the following scripts:

alter package &1 compile debug;

alter session set events='10938 trace name context level &1';

So I can now prepare a package for tracing with the following statement:

SQL> @compdbg PKGNAME

where PKGNAME is the name of my package. In the following session, I turn on tracing for all levels by passing 14 (2 + 4 + 8); then I call my PL/Vision substitute for DBMS_OUTPUT.PUT followed by the raising of an exception. The following code:

SQL> @trace 14
SQL> BEGIN
  2     p.l (1);
  3     raise no_data_found;
  4  END;
  5  /
begin
*
ERROR at line 1:
SQL> 

resulted in this trace file:

Dump file D:\ORANT\RDBMS80\trace\ORA00089.TRC
Wed Jun 11 13:22:52 1997
ORACLE V8.0.2.0.2 - Beta vsnsta=1
vsnsql=c vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Oracle8 Server Release 8.0.2.0.2 - Beta
With the distributed, heterogeneous, replication, objects
and parallel query options
PL/SQL Release 3.0.2.0.2 - Beta
Windows NT V4.0, OS V5.101, CPU type 586
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 11
pid: 59
Wed Jun 11 13:22:52 1997
*** SESSION ID:(11.18) 1997.06.11.13.22.52.431
------------ PL/SQL TRACE INFORMATION -----------
Levels set :  2    4    8
------------ PL/SQL TRACE INFORMATION -----------
Levels set :  2    4    8
Trace:   PACKAGE PLVPRO.P: P Stack depth = 2
Trace:   PACKAGE BODY PLVPRO.P: P Stack depth = 2
Trace:   PACKAGE BODY PLVPRO.P: L Stack depth = 2
Trace:   PACKAGE BODY PLVPRO.P: DISPLAY_LINE Stack depth = 3
Trace:   PACKAGE BODY PLVPRO.P: LINELEN Stack depth = 4
Trace:   PACKAGE BODY PLVPRO.P: PUT_LINE Stack depth = 4
Trace:   Pre-defined exception - OER 1403 at line 0 of ANONYMOUS BLOCK:

As you can see, trace files can get big fast, but they contain some extremely useful information.