UTL_CALL_STACK Oracle Database 12C release 1 (12.1)

New package UTL_CALL_STACK is added in Oracle 12C. The package gives more control for format display of call stack, error stack and error backtrace.

CALL STACK – it’s showing call stack from where it was started to where the call stack was examined. In previous release DBMS_UTILITY.FORMAT_CALL_STACK could be used to display it in constant format.

ERROR STACK – it shows full oracle error chain.  In previous release DBMS_UTILITY.FORMAT_ERROR_STACK could be used to display it in constant format.

ERROR BACKTRACE – The backtrace is a trace from where the exception was thrown to where the backtrace was examined. In previous release DBMS_UTILITY.FORMAT_ERROR_BACKTRACE could be used to display it in constant format.

For testing new package UTL_CALL_STACK I have created 3 simple procedures and extra function display_stack that shows new functionality

create or replace FUNCTION DISPLAY_STACK
(
  in_stack_type    IN VARCHAR2 DEFAULT 'BACKTRACE',
  in_stack_ver     IN VARCHAR2 DEFAULT 'OLD'
)
RETURN NUMBER AS 
  l_depth number;
BEGIN
  dbms_output.put_line('*******************************');
  dbms_output.put_line('**** '||in_stack_type||
                      ' version '||in_stack_ver||' ****');
  dbms_output.put_line('*******************************');

  IF in_stack_type='BACKTRACE' THEN
    IF in_stack_ver = 'OLD' THEN
      dbms_output.put(dbms_utility.format_error_backtrace);
    ELSIF in_stack_ver = 'NEW' THEN
      dbms_output.put_line
      (
        'Line  Unit       ' ||chr(10) ||
        '----- ------------- '
      );

      FOR i IN 1..utl_call_stack.backtrace_depth
      LOOP
        dbms_output.put_line
        (
          rpad(utl_call_stack.backtrace_line(i), 6) ||
          utl_call_stack.backtrace_unit(i)
        );
      END LOOP;
    END IF;  
  ELSIF in_stack_type = 'CALL' THEN
    IF in_stack_ver = 'OLD' THEN
      dbms_output.put(dbms_utility.format_call_stack);
    ELSIF in_stack_ver = 'NEW' THEN
      dbms_output.put_line
      (
        'Dpth Unit  Lex  Curr  Owner   Subprogram   ' ||chr(10) ||
        '     Line  Dpt  Ed                         ' ||chr(10) ||  
        '---- ----- ---- ----- ------- -------------'
      );

      FOR i IN 1..utl_call_stack.dynamic_depth
      LOOP
        dbms_output.put_line
        (
          rpad(i, 5, ' ') || 
          rpad(utl_call_stack.unit_line(i), 6, ' ') || 
          rpad(utl_call_stack.lexical_depth(i), 5, ' ') || 
          rpad(nvl(utl_call_stack.current_edition(i), ' '), 6) || 
          rpad(nvl(utl_call_stack.owner(i), ' '), 8) || 
          utl_call_stack.concatenate_subprogram
          (utl_call_stack.subprogram(i))
        );
      END LOOP;
    END IF;  
  ELSIF in_stack_type='ERROR' THEN
    IF in_stack_ver = 'OLD' THEN
      dbms_output.put(dbms_utility.format_error_stack);
    ELSIF in_stack_ver = 'NEW' THEN
      dbms_output.put_line
      (
        'Dpth Err  Message       ' ||chr(10) ||
        '     Num                ' ||chr(10) ||  
        '---- ---- ------------- '
      );

      FOR i IN 1..utl_call_stack.error_depth
      LOOP
        dbms_output.put
        (
          rpad(i, 5, ' ') || 
          rpad(utl_call_stack.error_number(i), 5) ||
          utl_call_stack.error_msg(i)
        );
      END LOOP;
    END IF;  
  END IF;

  dbms_output.put_line('*******************************'||CHR(10));
  
  RETURN 0;
END DISPLAY_STACK;


create or replace PROCEDURE prc_go1(l_call VARCHAR2)
IS
 l_num number;
BEGIN
  IF l_call = 'CALL' THEN
    l_num := display_stack('CALL', 'OLD');
    l_num := display_stack('CALL', 'NEW');
  ELSE
    l_num := 1/0;
  END IF;  
END;
/

create or replace PROCEDURE prc_go2(l_call VARCHAR2)
is
BEGIN
  prc_go1(l_call);
END;
/

create or replace PROCEDURE prc_go3(l_call VARCHAR2)
IS
  l_num number;
BEGIN
  prc_go2(l_call);
EXCEPTION
  WHEN OTHERS THEN
    RAISE TOO_MANY_ROWS;
END;
/

create or replace PROCEDURE prc_go4(l_call VARCHAR2)
IS
  l_num number;
BEGIN
  prc_go3(l_call);
EXCEPTION
  WHEN OTHERS THEN
    IF l_call = 'ERROR' THEN
      l_num := display_stack('ERROR', 'OLD');
      l_num := display_stack('ERROR', 'NEW');
    ELSE
      l_num := display_stack('BACKTRACE', 'OLD');
      l_num := display_stack('BACKTRACE', 'NEW');
    END IF;  
END;
/

By calling PRC_GO4  it starts cascade of calls PRC_GO3 -> PRC_GO2 -> PRC_GO1

CALL STACK

set serveroutput on
BEGIN
  prc_go4('CALL');
END;
/

*******************************
**** CALL version OLD ****
*******************************
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FF89FF47838        35  function TOMASZ.DISPLAY_STACK
00007FF8A55518C8         6  procedure TOMASZ.PRC_GO1
00007FF8E9823550         4  procedure TOMASZ.PRC_GO2
00007FF8E93F6708         5  procedure TOMASZ.PRC_GO3
00007FF8EB8AF110         5  procedure TOMASZ.PRC_GO4
00007FF8A5AF6DF0         2  anonymous block
*******************************

*******************************
**** CALL version NEW ****
*******************************
Dpth Unit  Lex  Curr  Owner   Subprogram   
     Line  Dpt  Ed                         
---- ----- ---- ----- ------- -------------
1    46    0          TOMASZ  DISPLAY_STACK
2    7     0          TOMASZ  PRC_GO1
3    4     0          TOMASZ  PRC_GO2
4    5     0          TOMASZ  PRC_GO3
5    5     0          TOMASZ  PRC_GO4
6    2     0                  __anonymous_block
*******************************

ERROR STACK

set serveroutput on
BEGIN
  prc_go4('ERROR');
END;
/

*******************************
**** ERROR version OLD ****
*******************************
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TOMASZ.PRC_GO3", line 8
ORA-01476: divisor is equal to zero
*******************************

*******************************
**** ERROR version NEW ****
*******************************
Dpth Err  Message       
     Num                
---- ---- ------------- 
1    1422 exact fetch returns more than requested number of rows
2    6512 at "TOMASZ.PRC_GO3", line 8
3    1476 divisor is equal to zero
*******************************

ERROR BACKTRACE

set serveroutput on
BEGIN
  prc_go4('BACKTRACE');
END;
/

*******************************
**** BACKTRACE version OLD ****
*******************************
ORA-06512: at "TOMASZ.PRC_GO3", line 8
ORA-06512: at "TOMASZ.PRC_GO4", line 5
*******************************

*******************************
**** BACKTRACE version NEW ****
*******************************
Line  Unit       
----- ------------- 
5     TOMASZ.PRC_GO4
8     TOMASZ.PRC_GO3
*******************************

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *