Oracle dinosaurs whoops I mean "experienced professionals" Smile 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.