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:
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:
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)
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.
Nice post, just one remark to add:
When logging call stack to a table, be sure to query all the data you want either inside (i.e. in the values clause) or outside (i.e. to local PL/SQL variables) of the insert SQL command context. SQL context adds an extra SQL element to the top of the call stack, so if you mix contexts, but use the same loop index i, you will get results messed up.
So do either as given in code scratch 1 or 2, but NOT 3:
code scratch 1:
============
for i in 1..utl_call_stack.dynamic_depth loop
— query utl_call_stack information using loop index i to local variables l_var_*
insert into my_call_stack_logging (columns…) values (l_var_*);
end loop;
code scratch 2:
============
for i in 1..utl_call_stack.dynamic_depth+1 loop
— query information to local variables l_var_*
insert into my_call_stack_logging (columns…) values (utl_call_stack method invocations using loop index i);
end loop;
code scratch 3: do NOT do this unless you know what you are doing:
========================================================
for i in 1..utl_call_stack.dynamic_depth loop
— query some utl_call_stack information using loop index i to local variables l_var_*
insert into my_call_stack_logging (columns…) values (l_var_*, utl_call_stack method invocations using loop index i);
end loop;