Oracle dinosaurs whoops I mean “experienced professionals” know that for decades we have used the DBMS_UTILITY package to obtain the current call stack within a PL/SQL program being executed. Here is a simple example showing that when we are deeply nested in PL/SQL package territory, we can get the current call stack.

SQL> create or replace
  2  procedure old_style_call_stack is
  3  begin
  4     dbms_output.put_line(
  5       dbms_utility.format_call_stack
  6       );
  7  end;
  8  /

Procedure created.

SQL> create or replace package pkg
  2  is
  3     procedure main_proc;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg
  2  is
  3    procedure proc_in_pkg is
  4      procedure inline_proc2 is
  5        procedure inline_proc3 is x int;
  6        begin
  7           old_style_call_stack;
  8           
  9        end;
 10      begin
 11        inline_proc3;
 12      end;
 13    begin
 14      inline_proc2;
 15    end;
 16
 17     procedure main_proc is
 18        procedure inline_proc1 is
 19        begin
 20          proc_in_pkg;
 21        end;
 22     begin
 23        inline_proc1;
 24     end;
 25  end;
 26  /

Package body created.

SQL>
SQL> set serverout on
SQL> begin
  2     pkg.main_proc;
  3  end;
  4  /
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x11e168060         3  procedure MCDONAC.OLD_STYLE_CALL_STACK
0x11e472ac0         7  package body MCDONAC.PKG
0x11e472ac0        11  package body MCDONAC.PKG
0x11e472ac0        14  package body MCDONAC.PKG
0x11e472ac0        20  package body MCDONAC.PKG
0x11e472ac0        23  package body MCDONAC.PKG
0x11e3efb20         2  anonymous block


PL/SQL procedure successfully completed.

SQL>
SQL>

But what is not immediately apparent from the output above, is that FORMAT_CALL_STACK returns a simple string. Whilst it may look like a set of rows, a small amendment to our routine to remove the line feeds shows what the call stack string really looks like:

SQL> create or replace
  2  procedure old_style_call_stack is
  3  begin
  4     dbms_output.put_line(
  5       replace(dbms_utility.format_call_stack,chr(10))
  6       );
  7  end;
  8  /

Procedure created.

SQL> create or replace package pkg
  2  is
  3     procedure main_proc;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg
  2  is
  3    procedure proc_in_pkg is
  4      procedure inline_proc2 is
  5        procedure inline_proc3 is x int;
  6        begin
  7           old_style_call_stack;
  8           
  9        end;
 10      begin
 11        inline_proc3;
 12      end;
 13    begin
 14      inline_proc2;
 15    end;
 16
 17     procedure main_proc is
 18        procedure inline_proc1 is
 19        begin
 20          proc_in_pkg;
 21        end;
 22     begin
 23        inline_proc1;
 24     end;
 25  end;
 26  /

Package body created.

SQL>
SQL> set serverout on
SQL> begin
  2     pkg.main_proc;
  3  end;
  4  /
----- PL/SQL Call Stack -----  object      line  object  handle    number  name 0x11e168060         3  procedure MCDONAC.OLD_STYLE_CALL_STACK0x11e472ac0         
7  package body MCDONAC.PKG0x11e472ac0        11  package body MCDONAC.PKG0x11e472ac0        14  package body MCDONAC.PKG0x11e472ac0        20  package 
body MCDONAC.PKG0x11e472ac0        23  package body MCDONAC.PKG0x11e3efb20         2  anonymous block


PL/SQL procedure successfully completed.

SQL>
SQL>

The UTL_CALL_STACK package improved upon this by storing the call stack information in an array structure so that the information at each depth in the call stack could be retrieved as required.

SQL> create or replace
  2  procedure better_call_stack is
  3  begin
  4     for i in reverse 1 .. utl_call_stack.dynamic_depth()
  5     loop
  6        dbms_output.put_line(
  7              rpad(utl_call_stack.lexical_depth(i),9)
  8           || rpad(to_char(utl_call_stack.unit_line(i),'99'),8)
  9           || utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i)));
 10     end loop;
 11  end;
 12  /

Procedure created.

SQL>
SQL> create or replace package pkg
  2  is
  3     procedure main_proc;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg
  2  is
  3    procedure proc_in_pkg is
  4      procedure inline_proc2 is
  5        procedure inline_proc3 is x int;
  6        begin
  7           --old_style_call_stack;
  8           better_call_stack;
  9        end;
 10      begin
 11        inline_proc3;
 12      end;
 13    begin
 14      inline_proc2;
 15    end;
 16
 17     procedure main_proc is
 18        procedure inline_proc1 is
 19        begin
 20          proc_in_pkg;
 21        end;
 22     begin
 23        inline_proc1;
 24     end;
 25  end;
 26  /

Package body created.

SQL>
SQL> set serverout on
SQL> begin
  2     pkg.main_proc;
  3  end;
  4  /
0          2     __anonymous_block
1         23     PKG.MAIN_PROC
2         20     PKG.MAIN_PROC.INLINE_PROC1
1         14     PKG.PROC_IN_PKG
2         11     PKG.PROC_IN_PKG.INLINE_PROC2
3          8     PKG.PROC_IN_PKG.INLINE_PROC2.INLINE_PROC3
0          5     BETTER_CALL_STACK

PL/SQL procedure successfully completed.

But the big benefit to UTL_CALL_STACK was more than just a cleanup of the data structures. Look back at the output above and you’ll see a critical improvement the UTL_CALL_STACK offers over DBMS_UTILITY. Notice that with the old style call stack, a package is treated as an atomic unit, and thus the names of the inline procedure names was lost.

So you might be thinking “Aw shoot, if I want that extra detail, now I will have to go back and adjust all my debugging code to move to UTL_CALL_STACK”. If you are indeed doing some refactoring, this is an approach I would recommend, however, you’ll be pleased to know that in the recent versions of the database, we have taken the additional information provided from UTL_CALL_STACK and pushed it into the old DBMS_UTILITY call as well. Here’s the same “old style” routine run on 19c

SQL> create or replace
  2  procedure old_style_call_stack is
  3  begin
  4     dbms_output.put_line(dbms_utility.format_call_stack);
  5  end;
  6  /

Procedure created.

SQL> create or replace package pkg
  2  is
  3     procedure main_proc;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg
  2  is
  3    procedure proc_in_pkg is
  4      procedure inline_proc2 is
  5        procedure inline_proc3 is x int;
  6        begin
  7           old_style_call_stack;
  8           
  9        end;
 10      begin
 11        inline_proc3;
 12      end;
 13    begin
 14      inline_proc2;
 15    end;
 16
 17     procedure main_proc is
 18        procedure inline_proc1 is
 19        begin
 20          proc_in_pkg;
 21        end;
 22     begin
 23        inline_proc1;
 24     end;
 25  end;
 26  /

Package body created.

SQL>
SQL> set serverout on
SQL> begin
  2     pkg.main_proc;
  3  end;
  4  /
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFE00CFA088         3  procedure MCDONAC.OLD_STYLE_CALL_STACK
00007FFE00ED34E8         7  package body MCDONAC.PKG.PROC_IN_PKG.INLINE_PROC2.INLINE_PROC3
00007FFE00ED34E8        11  package body MCDONAC.PKG.PROC_IN_PKG.INLINE_PROC2
00007FFE00ED34E8        14  package body MCDONAC.PKG.PROC_IN_PKG
00007FFE00ED34E8        20  package body MCDONAC.PKG.MAIN_PROC.INLINE_PROC1
00007FFE00ED34E8        23  package body MCDONAC.PKG.MAIN_PROC
00007FFE00D12C88         2  anonymous block


PL/SQL procedure successfully completed.

UTL_CALL_STACK is cool, but we’ve made it easier for you to get better debugging information even with the old style call stack code.