This third and last article on new PL/SQL features in Oracle Database 12c Release 1 focuses on the new UTL_CALL_STACK package.
Call Stacks, Error Stacks, and Error Backtraces
Prior to Oracle Database 12c, Oracle Database offered several DBMS_UTILITY functions to answer key questions programmers ask as they develop, debug, and maintain their code, and the functions have been extremely helpful. There is, however, always room for improvement, and that is why Oracle Database 12c added UTL_CALL_STACK.
Before I dive into UTL_CALL_STACK, here’s a refresher on the three DBMS_UTILITY functions that are reimagined by the new UTL_CALL_STACK package.
DBMS_UTILITY.FORMAT_CALL_STACK. Introduced in Oracle7, the DBMS_UTILITY.FORMAT_CALL_STACK built-in function returns a formatted string that shows the execution call stack: the sequence of invocations of procedures or functions that led to the point at which the function was called. In other words, this function answers the question, “How did I get here?”
Listing 1 demonstrates the DBMS_UTILITY.FORMAT_CALL_STACK function and what the formatted string looks like.
Code Listing 1: Demonstration of the DBMS_UTILITY.FORMAT_CALL_STACK function
SQL> CREATE OR REPLACE PROCEDURE proc1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); 5 END; 6 / SQL> CREATE OR REPLACE PACKAGE pkg1 2 IS 3 PROCEDURE proc2; 4 END pkg1; 5 / SQL> CREATE OR REPLACE PACKAGE BODY pkg1 2 IS 3 PROCEDURE proc2 4 IS 5 BEGIN 6 proc1; 7 END; 8 END pkg1; 9 / SQL> CREATE OR REPLACE PROCEDURE proc3 2 IS 3 BEGIN 4 FOR indx IN 1 .. 1000 5 LOOP 6 NULL; 7 END LOOP; 8 9 pkg1.proc2; 10 END; 11 / SQL> BEGIN 2 proc3; 3 END; 4 / ——————— PL/SQL Call Stack ——————— object handle line number object name 000007FF7EA83240 4 procedure HR.PROC1 000007FF7E9CC3B0 6 package body HR.PKG1 000007FF7EA0A3B0 9 procedure HR.PROC3 000007FF7EA07C00 2 anonymous block
This is very helpful information for tracing and error logging, but there are some drawbacks to using the DBMS_UTILITY.FORMAT_CALL_STACK function and the string it returns:
If you call a subprogram in a package, the formatted call stack will show only the package name, not the subprogram name and certainly not the names of nested subprograms defined within that packaged subprogram.
If you simply want the name of the most recently executed subprogram, you will have to parse the string. This is not hard to do, but it’s more code that you have to write and maintain.
The object handle value is, for all practical purposes, “noise.” PL/SQL developers—outside of Oracle, at least—never use it.
DBMS_UTILITY.FORMAT_ERROR_STACK. Also introduced in Oracle7, the DBMS_UTILITY.FORMAT_ERROR_STACK built-in function, like SQLERRM, returns the message associated with the current error (the value returned by SQLCODE).
The DBMS_UTILITY.FORMAT_ERROR_STACK function differs from SQLERRM in two ways:
It can return an error message as long as 1,899 characters, thereby avoiding (or at least making extremely unlikely) truncation issues when the error stack gets long. (SQLERRM truncates at only 510 characters.)
You cannot pass an error code number to this function, and it cannot be used to return the message for an error code.
As a rule, you should call this function inside your exception handler and then store the error stack in your error log for later analysis.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. Introduced in Oracle Database 10g, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE built-in function returns a formatted string that displays a stack of programs and line numbers tracing back to the line on which the error was originally raised.
This function closed a significant gap in PL/SQL functionality. In Oracle9i Database and earlier releases, once you handled an exception inside your PL/SQL block, you were unable to determine the line on which the error had occurred (perhaps the most important piece of information for developers).
If you did want to see this information, you had to allow the exception to go unhandled, at which point the full error backtrace was displayed on the screen or was otherwise presented to the user.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE generates extremely useful information. I suggest that whenever you handle an error, you call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function and write the trace to your error log table. It will aid greatly in resolving the cause of the error.
Yet, as with the DBMS_UTILITY .FORMAT_CALL_STACK function, the key information (the name of the subprogram and the number of the line on which the error was raised) is buried in the formatted trace string. And, even worse, you do not see the name of the subprogram within the package in which the error occurred.
All of these drawbacks are addressed by the new-in-Oracle Database 12c UTL_CALL_STACK package.The New UTL_CALL_STACK Package
The UTL_CALL_STACK package provides information about currently executing subprograms. Although the package name sounds as though it only provides information about the execution call stack, it also offers access to the error stack and error backtrace data.
Each stack contains depths (locations), and you can ask for the information at a certain depth in each of the three types of stacks made available through the package. This means that you no longer have to parse the formatted strings to find the specific information you need.
One of the greatest improvements of UTL_CALL_STACK over DBMS_UTILITY .FORMAT_CALL_STACK is that you can obtain a unit-qualified name, which concatenates the unit name, all lexical parents of the subprogram, and the subprogram name. This additional information is not available, however, for the error backtrace. Table 1 includes a list and descriptions of the subprograms in the UTL_CALL_STACK package.
|BACKTRACE_DEPTH||Returns the number of backtrace items in the backtrace|
|BACKTRACE_LINE||Returns the line number of the unit at the specified backtrace depth|
|BACKTRACE_UNIT||Returns the name of the unit at the specified backtrace depth|
|CONCATENATE_SUBPROGRAM||Returns a concatenated form of a unit-qualified name|
|DYNAMIC_DEPTH||Returns the number of subprograms in the call stack, including SQL, Java, and other non-PL/SQL contexts invoked along the way—for example, if A calls B calls C calls B, this stack, written as a line with dynamic depths underneath it, will look like this:
A B C B
|ERROR_DEPTH||Returns the number of errors in the call stack|
|ERROR_MSG||Returns the error message of the error at the specified error depth|
|ERROR_NUMBER||Returns the error number of the error at the specified error depth|
|LEXICAL_DEPTH||Returns the lexical nesting level of the subprogram at the specified dynamic depth|
|OWNER||Returns the owner name of the unit of the subprogram at the specified dynamic depth|
|UNIT_LINE||Returns the line number of the unit of the subprogram at the specified dynamic depth|
|SUBPROGRAM||Returns the unit-qualified name of the subprogram at the specified dynamic depth|
Let’s look first at how you can use UTL_CALL_STACK to emulate the DBMS_UTILITY.FORMAT_CALL_STACK function and display the complete call stack. To do this, you must iterate through the entries in the stack, identified by their depth. The format_call_stack_12c procedure in Listing 2 does precisely this.
Code Listing 2: The format_call_stack_12c procedure calls UTL_CALL_STACK subprograms
SQL> CREATE OR REPLACE PROCEDURE format_call_stack_12c 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ( 5 'LexDepth Depth LineNo Name'); 6 DBMS_OUTPUT.put_line ( 7 '-------- ----- ------ ----'); 8 9 FOR the_depth IN REVERSE 1 .. 10 utl_call_stack.dynamic_depth () 11 LOOP 12 DBMS_OUTPUT.put_line ( 13 RPAD ( 14 utl_call_stack.lexical_depth ( 15 the_depth), 16 9) 17 || RPAD (the_depth, 5) 18 || RPAD ( 19 TO_CHAR ( 20 utl_call_stack.unit_line ( 21 the_depth), 22 '99'), 23 8) 24 || utl_call_stack.concatenate_subprogram ( 25 utl_call_stack.subprogram ( 26 the_depth))); 27 END LOOP; 28 END; 29 /
Here are the key UTL_CALL_STACK package subprogram calls in Listing 2:
Lines 9 and 10 set up the numeric FOR loop and use the DYNAMIC_DEPTH function to start from the last entry in the stack and go in reverse back to the first entry in the stack.
Line 14 calls the LEXICAL_DEPTH function to display the depth in the stack of each entry.
Lines 20 and 21 call UNIT_LINE to get the line number of the program unit.
Lines 24 and 25 first call SUBPROGRAM to get the entry in the stack at the current depth. CONCATENATE_SUBPROGRAM then obtains the fully qualified name of that subprogram.
I then use the format_call_stack_12c procedure (in Listing 2) in the pkg.do_stuff procedure and execute that procedure, as shown in Listing 3.
Code Listing 3: The pkg.do_stuff procedure calls the format_call_stack_12c procedure
SQL> CREATE OR REPLACE PACKAGE pkg 2 IS 3 PROCEDURE do_stuff; 4 END; 5 / SQL> CREATE OR REPLACE PACKAGE BODY pkg 2 IS 3 PROCEDURE do_stuff 4 IS 5 PROCEDURE np1 6 IS 7 PROCEDURE np2 8 IS 9 PROCEDURE np3 10 IS 11 BEGIN 12 format_call_stack_12c; 13 END; 14 BEGIN 15 np3; 16 END; 17 BEGIN 18 np2; 19 END; 20 BEGIN 21 np1; 22 END; 23 END; 24 / SQL> BEGIN 2 pkg.do_stuff; 3 END; 4 / LexDepth Depth LineNo Name ——————— ——————— ———————— —————————————————————————— 0 6 2 __anonymous_block 1 5 21 PKG.DO_STUFF 2 4 18 PKG.DO_STUFF.NP1 3 3 15 PKG.DO_STUFF.NP1.NP2 4 2 12 PKG.DO_STUFF.NP1.NP2.NP3 0 1 12 FORMAT_CALL_STACK_12C
Next I use the UTL_CALL_STACK package to show the name of the program unit and the line number in that unit where the current exception was raised. In Listing 4, I create and execute a function named BACKTRACE_TO that “hides” the calls to the UTL_CALL_STACK subprograms. In each call to BACKTRACE_UNIT and BACKTRACE_LINE, I pass the value returned by the ERROR_DEPTH function.
Code Listing 4: The backtrace_to function calls UTL_CALL_STACK subprograms
SQL> CREATE OR REPLACE FUNCTION backtrace_to 2 RETURN VARCHAR2 3 IS 4 BEGIN 5 RETURN 6 utl_call_stack.backtrace_unit ( 7 utl_call_stack.error_depth) 8 || ' line ' 9 || 10 utl_call_stack.backtrace_line ( 11 utl_call_stack.error_depth); 12 END; 13 / SQL> CREATE OR REPLACE PACKAGE pkg1 2 IS 3 PROCEDURE proc1; 4 PROCEDURE proc2; 5 END; 6 / SQL> CREATE OR REPLACE PACKAGE BODY pkg1 2 IS 3 PROCEDURE proc1 4 IS 5 PROCEDURE nested_in_proc1 6 IS 7 BEGIN 8 RAISE VALUE_ERROR; 9 END; 10 BEGIN 11 nested_in_proc1; 12 END; 13 14 PROCEDURE proc2 15 IS 16 BEGIN 17 proc1; 18 EXCEPTION 19 WHEN OTHERS THEN RAISE NO_DATA_FOUND; 20 END; 21 END pkg1; 22 / SQL> CREATE OR REPLACE PROCEDURE proc3 2 IS 3 BEGIN 4 pkg1.proc2; 5 END; 6 / SQL> BEGIN 2 proc3; 3 EXCEPTION 4 WHEN OTHERS 5 THEN 6 DBMS_OUTPUT.put_line (backtrace_to); 7 END; 8 / HR.PKG1 line 19
Note that the depth value for the error backtrace is different from the depth value for the call stack. With the call stack, 1 is the top of the stack (the currently executing subprogram). With the error backtrace, the location in my code where the error was raised is found at ERROR_DEPTH, not 1.
With UTL_CALL_STACK there is no longer any need to parse the complete backtrace string, as would be necessary with DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. Instead, I can very surgically find, display, and/or log the key information I need.
Here are some things to keep in mind about UTL_CALL_STACK:
Compiler optimizations can change lexical, dynamic, and backtrace depth, because the optimization process can mean that subprogram invocations are skipped.
UTL_CALL_STACK is not supported past remote procedure call boundaries. For example, if proc1 calls remote procedure remoteproc2, remoteproc2 will not be able to obtain information about proc1 by using UTL_CALL_STACK.
Lexical unit information is not exposed through UTL_CALL_STACK. Instead, you can use PL/SQL conditional compilation to obtain that information.
UTL_CALL_STACK is a very handy utility, but for real-world use, you will likely need to build some utilities of your own around this package’s subprograms. I have built a helper package with utilities I think you’ll find helpful. You can find this code in the 12c_utl_call_stack_helper.sql and 12c_utl_call_stack_helper_demo.sql files.Better Diagnostics, Better Programming
The three DBMS_UTILITY functions (DBMS_UTILITY.FORMAT_CALL_STACK, DBMS_UTILITY.FORMAT_ERROR_STACK, and DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE) have been crucial aids in diagnosing and resolving problems in PL/SQL code. The UTL_CALL_STACK package recognizes the importance of this data and takes a big step forward in giving PL/SQL developers access to more in-depth and useful information.
Each Oracle Magazine PL/SQL article by Steven Feuerstein offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at the PL/SQL Challenge (plsqlchallenge.com), a website that offers online quizzes on the PL/SQL language as well as SQL, Oracle Application Express, database design, and deductive logic.
Here is your quiz for this article.
I create a package specification:
CREATE OR REPLACE PACKAGE plch_pkg IS PROCEDURE proc1; END plch_pkg; /Which of the choices create a body for this package so that after the following block executes, the text NESTED_IN_PROC1 will appear on the screen?
CREATE OR REPLACE PACKAGE BODY plch_pkg IS PROCEDURE proc1 IS PROCEDURE nested_in_proc1 IS BEGIN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); END; BEGIN nested_in_proc1; END; END plch_pkg; /
CREATE OR REPLACE PACKAGE BODY plch_pkg IS PROCEDURE proc1 IS PROCEDURE nested_in_proc1 IS BEGIN DBMS_OUTPUT.put_line ( UTL_CALL_STACK.CONCATENATE_SUBPROGRAM ( UTL_CALL_STACK.SUBPROGRAM (1))); END; BEGIN nested_in_proc1; END; END plch_pkg; /
CREATE OR REPLACE PACKAGE BODY plch_pkg IS PROCEDURE proc1 IS PROCEDURE nested_in_proc1 IS BEGIN DBMS_OUTPUT.put_line ($$plsql_unit); END; BEGIN nested_in_proc1; END; END plch_pkg; /
TEST your PL/SQL knowledge
READ more Feuerstein
Photography by Scott Webb, Unsplash