Sophisticated Call Stack Analysis

UTL_CALL_STACK package in Oracle Database 12c gives developers better answers.

By Steven Feuerstein

January/February 2014

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 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.

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.backtrace_depth)
  8        || ' line '
  9        ||
 10        utl_call_stack.backtrace_line (
 11           utl_call_stack.backtrace_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.

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.

CREATE OR REPLACE PACKAGE plch_pkg
IS
   PROCEDURE proc1;
END plch_pkg;
/

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;
/

DOWNLOAD
Oracle Database 12c
Steven Feuerstein’s demo.zip SQL files

TEST your PL/SQL knowledge

READ more Feuerstein

READ more about
Oracle Database 12c
PL/SQL