The PLVtrc (PL/Vision TRaCe) package offers a generic trace facility for PL/SQL applications. It is especially useful if you do not have access to a source debugger for PL/SQL . It is also used by PLVexc to record the currently executing PL/SQL program unit when an exception occurs.
The PLVtrc package offers several capabilities:
The elements provided by PLVtrc can be broken down into three areas: output control, module tracking, and activity trace. They are explored in the following sections.
21.2.1 Directing Output from PLVtrc
As with many other PL/Vision packages, PLVtrc allows you to programmatically control its behavior, in this case its output. PLVtrc offers three different types of output:
PROCEDURE turn_on; PROCEDURE turn_off; FUNCTION tracing RETURN BOOLEAN;
To activate the trace facility, enter:
SQL> execute PLVtrc.turn_on;
To de-activate the trace facility enter:
SQL> execute PLVtrc.turn_off;
PROCEDURE log; PROCEDURE nolog; FUNCTION logging RETURN BOOLEAN;
This command turns on logging of trace message:
SQL> execute PLVtrc.log;
To turn off logging, enter:
SQL> execute PLVtrc.nolog;
So to include the module in trace messages, enter:
SQL> execute PLVtrc.dispmod;
To ignore the module name enter:
SQL> execute PLVtrc.nodispmod;
To turn on all these options, you need to execute all three "on" procedures:
SQL> exec PLVtrc.dispmod; SQL> exec PLVtrc.log; SQL> exec PLVtrc.turn_on;
The order in which you call these toggle programs is not important. Just remember that you cannot write information to the log or display the current module unless the overall trace is turned on.
In all examples above I have shown the syntax for executing the programs from within SQL*Plus. You can also call these programs from within a PL/SQL program, in which case you would not use the execute command.
21.2.2 Accessing the PL/SQL Call Stack
The PLVtrc package provides you with the ability to access and parse the call stack maintained by the PL/SQL runtime engine. This call stack is available with a call to the DBMS_UTILITY. FORMAT_CALL_STACK function. Here is an example of the string returned by this function:
----- PL/SQL Call Stack ----- object line object handle number name 88ce3f74 8 package STEVEN.DISPCSTK 88e49fc4 2 function STEVEN.COMPANY_TYPE 88e384c8 1 procedure STEVEN.CALC_PROFITS 88e3823c 1 procedure STEVEN.CALC_TOTALS 88e49390 1 procedure STEVEN.CALC_NET_WORTH 88e2bd20 1 anonymous block
The string actually contains many newline characters (you can find these by searching for CHR(10) with the INSTR function). It is designed for easy display, but not easy manipulation within a programmatic setting.
The PLVtrc package offers two programs to access this PL/SQL call stack:
PLVtrc offers two programs to trace the execution of your code: action and show . The action program's header is:
You pass a string, a numeric counter or indicator, and another string that is used as a prefix on the trace message. The action procedure is used by startup , terminate , and the other activity trace module, show . It is, in other words, the lowest-level trace procedure.
The show procedure is heavily overloaded. Like the p.l procedure, the show program comes in many flavors of argument combinations, as shown in the list below. This is done to make it easy for you to pass different combinations of data for display without having to perform TO_CHAR conversions and concatenations.
The following datatype combinations are supported by PLVtrc.show :
Here are the headers for the single-value show procedures:
PROCEDURE show (stg1 IN VARCHAR2); PROCEDURE show (date1 IN DATE, mask_in IN VARCHAR2 := PLV.datemask); PROCEDURE show (bool1 IN BOOLEAN); PROCEDURE show (num1 IN NUMBER);
Here are the headers for the double-value show procedures:
PROCEDURE show (stg1 IN VARCHAR2, num1 IN NUMBER); PROCEDURE show (stg1 IN VARCHAR2, date1 IN DATE, mask_in IN VARCHAR2 := PLV.datemask); PROCEDURE show (stg1 IN VARCHAR2, bool1 IN BOOLEAN);
Here are the headers for the triple-value show procedures:
PROCEDURE show (stg1 IN VARCHAR2, num1 IN NUMBER, num2 IN NUMBER); PROCEDURE show (stg1 IN VARCHAR2, num1 IN NUMBER, date1 IN DATE, mask_in IN VARCHAR2 := PLV.datemask); PROCEDURE show (stg1 IN VARCHAR2, num1 IN NUMBER, bool1 IN BOOLEAN);
You can place calls to both action and show in your programs. No output is generated from these message lines until you turn on the trace. And since there is very little overhead involved in calling these programs, you can leave the trace in your code even when it goes into production status. When you have to debug the code, you simply call PLVtrc.turn_on , run the application, and you have a wealth of information available to you.
21.2.4 The PLVtrc Program Stack
PLVtrc offers two programs to build its own program execution stack: startup and terminate . You can also get information about the current and previous modules of the stack.
The PLVtrc call stack operations provide two key advantages over the builtin:
You should call PLVtrc.startup as the first line in the body of your program. Its header is:
PROCEDURE startup (module_in IN VARCHAR2, string_in IN VARCHAR2 := NULL);
You provide the module name or abbreviation or whatever string you want to record as representing the program. You can also pass in a second string argument. This value is displayed or logged by PLVtrc, according to the toggle settings. This second argument allows you to pass variable data into the trace.
The terminate program performs a task opposite that of startup : it pops off the stack the most recently pushed module and sets the previous module variable. The header for terminate is:
PROCEDURE terminate (string_in IN VARCHAR2 := NULL);
As with startup , you can provide a string to be displayed or logged, depending on the status of the PLVtrc toggles. You should call PLVtrc.terminate as the last executable statement in your procedure, and immediately before your RETURN statement in a function.
You should also call PLVtrc.terminate in each of your exception handlers in a PL/SQL block where startup was called. Otherwise the enclosing module will not be popped off the stack when the block fails.
If you use one of the high-level handlers of PLVexc to handle your exception, however, you do not have to -- and should not -- call terminate . Those handlers do that for you.
Each time PLVtrc.startup is executed, it pushes the current module onto the PLVtrc execution stack and sets the current module to the first argument in the call to startup .
FUNCTION currmod RETURN VARCHAR2;
FUNCTION prevmod RETURN VARCHAR2;
The PLVexc package makes use of this function so that it can record the program in which an exception was raised. You might not have too much use for prevmod .
See Section 21.2.5, "Using PLVtrc" to see how you put these pieces together.
You will want to use this program when you have finished running a test and you want to make sure that there aren't any extraneous module names left on the stack.
You can display the contents of the stack with a call to showecs ; the header is:
This program, in turn, calls PLVlst.display to display the contents of the list, which comprise the underlying data structure for the stack (implemented, actually, with the PLVstk package -- an interesting exercise in code layering).
If you do not want to directly display the PLVtrc stack, you can extract it as a string in much the same format as that provided by the builtin FORMAT_CALL_STACK function with the ecs_string function. Its header is:
FUNCTION ecs_string RETURN VARCHAR2;
Each module name in the call stack is separated by a newline character.
FUNCTION module (pos_in IN INTEGER := c_top_pos) RETURN VARCHAR2;
where pos_in is the position in the stack in which you are interested. The current program is stored in the top-most position of 0 (actually not yet on the call stack) and is encapsulated in the package constant, c_top_pos . To obtain the name of the module that called the current program, you would pass in a position of 1.
The following examples show you how to use the different elements of the PLVtrc package.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.