Oracle: UTL_CALL

Backtrace structure

The backtrace structure is not a stack. It is a list that is operated on on both sides.

Exception

When an exception occurs, the backtrace structure is emptied. Then the location (unit and line number) of the exception is pushed onto the backtrace stack.

Leaving a body of a function or procedure

When the body of a function or procedure is left, and an exception is «active», the location of the call stack with the lowest index is put on the side of the backtrace structure with the highest index.

The body of the function/procedure is left either

if execution is returned to the caller or the function, or
if the execution enters into the (optional) exception handler of the function/procedure)

RAISE statement

The raise statement is special because it operates on the «other» end of the backtrace structure.

In particular, it puts the location that contains the raise statement ot the side of the backtrace structure with the lowest index.

Error stack

If the third parameter of raise_application_error is true, the error message is put on top of the error stack, if false, the error stack is replaced with the new error message.

The error stack can also be gotten as a string of up to 2000 bytes with dbms_utility.format_error_stack

Functions that return depths

There are three functions that return a pls_integer that correspond to different «depths»:

`dynamic_depth`	The dynamic depth is increased when a subprogram (function or procedure) is called and decreased again when the subprogram returns to the caller.
`backtrace_depth`	starts with `0`. Is increased by an exception, a `raise` statement (in an exception handler) or by leaving a procedure/function without handling the exception.
`error_depth`	Often the same value as `backtrace_depth`. Might have a different value for «multi-line» exceptions such as demonstrated in this example.

Functions that take dynamic_depth as argument

The following functions have an argument named dynamic_depth. The name of this argument is, imho, confusing because in order to get the information about the currently active stack frame, the value 1 (not the value is returned by utl_call_stack.dynamic_depth) needs to be passed.

The value 2 gets the information of the calling frame, and so on.

`lexical_depth`	Returns 1 for non-nested sub-programs, increases by one for each nesting level
`subprogram`	returns a `varray` that contains the lexical parents of a subprogram. The value of `subprogram(d)(1)` is the name of the PL/SQL object of the corresponding stackframe, the value of `subprogram(d)(subprogram(d).count)` is the name of the function/procedure of the corresponding stack frame. This varray can then be converted into a `varchar2` with `concatenate_subprogram`
`owner`
`unit_line`	A `pls_integer` that corresponds to the line number in the source code of the corresponding stack frame.
`unit_type`	`PACKAGE BODY` etc.
`actual_edition`
`current_edition`	deprecated

Procedures that take error_depth as argument

`error_number`
`error_msg`

Procedures that take a backtrace_depth as argument

`backtrace_unit`	The name (`varchar2`) …
`backtrace_line`	… and line (`pls_integer`) of the unit at the specified backtrace unit.

Other functions

concatenate_subprogram see subprogram

Simple example

create or replace package tq84_call_stack_test as

    procedure a;


end tq84_call_stack_test;
/

create or replace package body tq84_call_stack_test as

    procedure d is begin
      raise_application_error(-20801, 'error raised in d');
    end d;

    procedure c is begin d; end c;
    procedure b is begin c; end b;

    procedure a is begin
      b;
    exception when others then
      dbms_output.put_line('exception caught in a, backtrace_depth:  ' || utl_call_stack.backtrace_depth);

      for i in 1 .. utl_call_stack.backtrace_depth loop
          dbms_output.put_line('  ' || i || ' @ ' || utl_call_stack.backtrace_line(i));
      end loop;
    end a;

end tq84_call_stack_test;
/
show errors

exec tq84_call_stack_test.a

drop package tq84_call_stack_test;

Github repository Oracle-Patterns, path: /Installed/utl/call_stack/example-01.sql

Slightly more complicated example

create or replace package utl_call_stack_test as

    procedure run;
    procedure showStack(i_am varchar2);

end utl_call_stack_test;
/


create or replace package body utl_call_stack_test as

    procedure showStack(i_am varchar2) is
    begin
        dbms_output.new_line;
        dbms_output.put_line('Current stack for  ' || i_am);
        dbms_output.put_line('  dynamic depth:   ' || utl_call_stack.dynamic_depth);
        dbms_output.put_line('  error depth:     ' || utl_call_stack.error_depth);
        dbms_output.put_line('  backtrace depth: ' || utl_call_stack.backtrace_depth);
        dbms_output.new_line;

        for depth in /*reverse*/ 1 .. utl_call_stack.dynamic_depth loop
            dbms_output.put_line(
               to_char(UTL_CALL_STACK.lexical_depth(depth),    '90') || ' ' ||
               rpad   (UTL_CALL_STACK.unit_type    (depth),     30 ) || ' ' ||
               rpad   (UTL_CALL_STACK.subprogram   (depth)(1) , 30 ) || ' ' ||
               to_char(UTL_CALL_STACK.unit_line    (depth), '99990') || ' ' ||
                       UTL_CALL_STACK.concatenate_subprogram(
                       UTL_CALL_STACK.subprogram   (depth)
                       )
            );
        end loop;

        if utl_call_stack.error_depth > 0 then
           dbms_output.new_line;
        end if;

        for error in 1 ..  utl_call_stack.error_depth loop

            dbms_output.put_line('   ' ||
               rpad   (utl_call_stack.error_msg   (error), 100)    || ' ' ||
               to_char(utl_call_stack.error_number(error), '99990')
            );

        end loop;

        if utl_call_stack.backtrace_depth > 0 then
           dbms_output.new_line;
        end if;

        for backtrace in 1 ..  utl_call_stack.backtrace_depth loop

            dbms_output.put_line('   ' ||
               rpad   (utl_call_stack.backtrace_unit(backtrace), 61) || ' ' ||
               to_char(utl_call_stack.backtrace_line(backtrace), '99990')
            );

        end loop;

    end showStack;

    procedure PROC is
        procedure NESTED_PROC is
            i integer;
        begin
            showStack('NESTED_PPROC');

            i := 42/0;

        exception when others then
            showStack('Exception PROC');
            raise;
        end NESTED_PROC;
    begin
        showStack('PROC');
        NESTED_PROC;
    end PROC;

    procedure run is begin
        showStack('run');
        PROC;
    exception when others then
        showStack('exception run');
    end run;

end utl_call_stack_test;
/

show errors

exec utl_call_stack_test.showStack('SQL*Plus');
exec utl_call_stack_test.run

Github repository Oracle-Patterns, path: /Installed/utl/call_stack/nested.sql

When executed in SQL*Plus, this script prints

Current stack for  SQL*Plus
  dynamic depth:   2
  error depth:     0
  backtrace depth: 0

  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                13 UTL_CALL_STACK_TEST.SHOWSTACK
  0 ANONYMOUS BLOCK                __anonymous_block                   1 __anonymous_block

Current stack for  run
  dynamic depth:   3
  error depth:     0
  backtrace depth: 0

  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                13 UTL_CALL_STACK_TEST.SHOWSTACK
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                70 UTL_CALL_STACK_TEST.RUN
  0 ANONYMOUS BLOCK                __anonymous_block                   1 __anonymous_block

Current stack for  PROC
  dynamic depth:   4
  error depth:     0
  backtrace depth: 0

  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                13 UTL_CALL_STACK_TEST.SHOWSTACK
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                65 UTL_CALL_STACK_TEST.PROC
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                71 UTL_CALL_STACK_TEST.RUN
  0 ANONYMOUS BLOCK                __anonymous_block                   1 __anonymous_block

Current stack for  NESTED_PPROC
  dynamic depth:   5
  error depth:     0
  backtrace depth: 0

  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                13 UTL_CALL_STACK_TEST.SHOWSTACK
  2 PACKAGE BODY                   UTL_CALL_STACK_TEST                56 UTL_CALL_STACK_TEST.PROC.NESTED_PROC
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                66 UTL_CALL_STACK_TEST.PROC
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                71 UTL_CALL_STACK_TEST.RUN
  0 ANONYMOUS BLOCK                __anonymous_block                   1 __anonymous_block

Current stack for  Exception PROC
  dynamic depth:   5
  error depth:     1
  backtrace depth: 1

  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                13 UTL_CALL_STACK_TEST.SHOWSTACK
  2 PACKAGE BODY                   UTL_CALL_STACK_TEST                61 UTL_CALL_STACK_TEST.PROC.NESTED_PROC
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                66 UTL_CALL_STACK_TEST.PROC
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                71 UTL_CALL_STACK_TEST.RUN
  0 ANONYMOUS BLOCK                __anonymous_block                   1 __anonymous_block

   divisor is equal to zero                                                                               1476

   DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST                            58

Current stack for  exception run
  dynamic depth:   3
  error depth:     4
  backtrace depth: 4

  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                13 UTL_CALL_STACK_TEST.SHOWSTACK
  1 PACKAGE BODY                   UTL_CALL_STACK_TEST                73 UTL_CALL_STACK_TEST.RUN
  0 ANONYMOUS BLOCK                __anonymous_block                   1 __anonymous_block

   divisor is equal to zero                                                                               1476
   at "DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST", line 62                                                   6512
   at "DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST", line 58                                                   6512
   at "DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST", line 66                                                   6512

   DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST                            62
   DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST                            58
   DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST                            66
   DWH_DM_RISKFINANCE.UTL_CALL_STACK_TEST                            71

Oracle: UTL_CALL_STACK