Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack image76

Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack

 

Oracle Database 12c ships with a brand new supplied package called UTL_CALL_STACK. This package provides an API for inspecting the PL/SQL Callstack. The package complements the DBMS_ UTILITY.FORMAT_CALL_STACK that returns a pretty print human readable overview of the callstack.

Here is an example of how this package could be used in your own PL/SQL code:

image

Dynamic depth is an indication for the number of items there are on the call stack underneath the current scope. The entire stack can be traversed, from 1 (the very first PL/SQL unit that started the whole thing) to the current one. For each level on the call stack, the name of the subprogram can be retrieved, as well as the line number from where the call was made to the next level in the call stack.

Putting this into action on a simple piece of code – we can get the following results:

image

Note how in the code the unit c makes a call to unit a that subsequently invokes unit a. On each level, we invoke the tell_on_call_stack procedure that was introduced in the previous figure. This then show us the call stack at every stage in this little package. The deepest we go is in procedure b that reports a stack that consists of B, A, C and an anonymous block from which the call to C was originally made.

Package UTL_CALL_STACK provides a number of procedures and functions that can be invoked to find out more about the current PL/SQL stack. This includes functions for retrieving

  • BACKTRACE: •DEPTH, LINE and UNIT
  • ERROR: •DEPTH, MSG and NUMBER
  • OWNER, SUBPROGRAM, UNIT_LINE
  • LEXICAL DEPTH (NESTING LEVEL)

image

Resources

Download Oracle Database 12c at eDelivery or from OTN: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.

Oracle Database 12c Documentation: http://www.oracle.com/pls/db121/homepage. Specific section on UTL_CALL_STACK: http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/u_call_stack.htm#CHDIIEGH.

One Response

  1. Jozsef Marton June 4, 2015