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