Oracle PL/SQL Programming: Guide to Oracle8i Features

Earlier versions of Oracle offered some PL/SQL trace capabilities, but Oracle8 i provides an API that allows you to more easily specify and control the tracing of the execution of PL/SQL procedures, functions, and exceptions. DBMS_TRACE 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.

7.2.1 Installing DBMS_TRACE

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 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 and run the following files in the order specified:

\Oracle\Ora81\Rdbms\Admin\dbmspbt.sql

\Oracle\Ora81\Rdbms\Admin\prvtpbt.plb

7.2.2 DBMS_TRACE Programs

The programs in the DBMS_TRACE package are listed in Table 7.5.

Table 7.5. DBMS_TRACE Programs

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:

DBMS_TRACE.trace_all_calls CONSTANT INTEGER := 1; DBMS_TRACE.trace_enabled_calls CONSTANT INTEGER := 2; DBMS_TRACE.trace_all_exceptions CONSTANT INTEGER := 4; DBMS_TRACE.trace_enabled_exceptions CONSTANT INTEGER := 8;

To turn on tracing from all programs executed in your session, issue this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_calls);

To turn on tracing for all exceptions raised during the session, issue this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

You then run your code; when you are done, you stop the trace session by calling:

DBMS_TRACE.CLEAR_PLSQL_TRACE;

You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will mostly need to pay attention to the modification date of the files to figure out which file to examine. The location of the trace files is discussed later in Section 7.2.4. You cannot use PL/SQL tracing with the multithreaded server (MTS).

7.2.3 Controlling Trace File Contents

The trace files produced by DBMS_TRACE can get really big. You can minimize the trace output and focus it by obtaining trace information only for specific programs that you have enabled for trace data collection.

To enable a specific program for tracing, you can alter the session to enable any programs that are created or replaced in the session. To take this approach, issue this command:

ALTER SESSION SET PLSQL_DEBUG=TRUE;

If you don't want to alter your entire session, you can recompile a specific program unit in debug mode as follows (not applicable to anonymous blocks):

ALTER [PROCEDURE FUNCTION PACKAGE BODY] program_name COMPILE DEBUG;

After you have enabled the programs in which you're interested, the following call will initiate tracing just for those program units:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

You can also restrict the trace information to only those exceptions raised within enabled programs with this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

If you request tracing for all programs or exceptions and also request tracing only for enabled programs or exceptions, the request for "all" takes precedence.

7.2.4 Format of Collected Data

If you request tracing only for enabled program units and the current program unit is not enabled, then no trace data is written. If the current program unit is enabled for tracing, then call tracing writes out the program unit type, name , and stack depth. If the current program unit is not enabled, then call tracing writes out the program unit type, line number, and stack depth.

Exception tracing writes out the line number. Raising an exception records trace information on whether the exception is user defined or predefined, and records the exception number in the case of predefined exceptions. If you raise a user -defined exception, you will always see an error code of 1.

In Oracle8 i under Windows NT, the trace files are written to the following directory (by default):

Oracle\Admin\Oracle81\udump

Here is an example of the output from a trace of the procedure showemps:

*** 1999.06.14.09.59.25.394 *** SESSION ID:(9.7) 1999.06.14.09.59.25.344 ------------ PL/SQL TRACE INFORMATION ----------- Levels set : 1 Trace: ANONYMOUS BLOCK: Stack depth = 1 Trace: PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2 Trace: PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3 Trace: PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4 Trace: PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4 Trace: PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3 Trace: PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3 Trace: PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3 Trace: PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3 Trace: PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4