Oracle8i Supplied Packages Reference
Release 8.1.5

A68001-01

Library

Product

Contents

Index

Prev Next

50
DBMS_TRACE

Oracle8i PL/SQL provides an API for tracing the execution of PL/SQL programs on the server. You can use the Trace API, implemented on the server as the DBMS_TRACE package, to trace PL/SQL functions, procedures, and exceptions.

DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. The trace data gets collected as the program executes, and it is written out to the Oracle Server trace file.

A typical session involves:

Requirements

This package must be created under SYS.

Restrictions

You cannot use PL/SQL tracing with the multi-threaded server (MTS).

Constants

trace_all_calls          constant INTEGER := 1; 
trace_enabled_calls      constant INTEGER := 2; 
trace_all_exceptions     constant INTEGER := 4; 
trace_enabled_exceptions constant INTEGER := 8; 
/* 
 * The version of the trace package. These constants will change as the 
 * package evolves. Use the PLSQL_TRACE_VERSION procedure (described below) 
 * to get the current version of the package. 
 */ 
trace_major_version      constant BINARY_INTEGER := 1; 
trace_minor_version      constant BINARY_INTEGER := 0; 

Using DBMS_TRACE

Controlling Data Volume

Profiling large applications may produce a huge volume of data. You can control the volume of data collected by enabling specific program units for trace data collection.

You can enable a program unit by compiling it debug. This can be done in one of two ways:

alter session set plsql_debug=true; 
create or replace ... /* create the library units - debug information will be 
generated */ 

or:

/* recompile specific library unit with debug option */ 
alter [PROCEDURE | FUNCTION | PACKAGE BODY]  <libunit-name> compile debug; 


Note:

This second method cannot be used for anonymous blocks.  


Collecting Trace Data

Tracing Calls

Two levels of call tracing are available:

Enabling cannot be detected for remote procedure calls (RPCs); hence, RPCs are only traced with level 1.

Tracing Exceptions

Two levels of exception tracing are available:

Collected Data

If tracing is requested only for enabled program units, and if the current program unit is not enabled, then no trace data is written.

If the current program unit is enabled, 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 the stack depth.

Exception tracing writes out the line number. Raising the exception shows information on whether the exception is user-defined or pre-defined and the exception number, in the case of pre-defined exceptions.

Summary of Subprograms

Table 50-1 DBMS_TRACE Package Subprograms
Subprogram  Description 
SET_PLSQL_TRACE procedure
 

Starts tracing in the current session.  

CLEAR_PLSQL_TRACE procedure
 

Stops trace data dumping in session.  

PLSQL_TRACE_VERSION 
procedure
 

Gets the version number of the trace package.  

SET_PLSQL_TRACE procedure

This procedure enables PL/SQL trace data collection.

Syntax

DBMS_TRACE.SET_PLSQL_TRACE ( 
   trace_level INTEGER); 

Parameters

Table 50-2 SET_PLSQL_TRACE Procedure Parameters
Parameter  Description 
trace_level
 

This must be one of the constants trace_all_calls, trace_enabled_calls, trace_all_exceptions, or trace_enabled_exceptions.

See "Collecting Trace Data" for more information.  

CLEAR_PLSQL_TRACE procedure

This procedure disables trace data collection.

Syntax

DBMS_TRACE.CLEAR_PLSQL_TRACE; 

Parameters

None.

PLSQL_TRACE_VERSION procedure

This procedure gets the version number of the trace package. It returns the major and minor version number of the DBMS_TRACE package.

Syntax

DBMS_TRACE.PLSQL_TRACE_VERSION ( 
   major OUT BINARY_INTEGER, 
   minor OUT BINARY_INTEGER); 

Parameters

Table 50-3 PLSQL_TRACE_VERSION Procedure Parameters
Parameter  Description 
major
 

Major version number of DBMS_TRACE.  

minor
 

Minor version number of DBMS_TRACE.  




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index