CREATE OR REPLACE PACKAGE my_utl_call_stack
AUTHID DEFINER
IS
FUNCTION format_call_stack RETURN VARCHAR2;
FUNCTION format_error_stack RETURN VARCHAR2;
FUNCTION format_error_backtrace RETURN VARCHAR2;
FUNCTION backtrace_to RETURN VARCHAR2;
END;Package created.
CREATE OR REPLACE PACKAGE BODY my_utl_call_stack
IS
FUNCTION format_call_stack
RETURN VARCHAR2
IS
l_return VARCHAR2 (32767)
:= '----- PL/SQL Call Stack -------'
|| CHR (10)
|| 'Line Object name'
|| CHR (10)
|| '-------- ----------------------';
BEGIN
/* 1 is always this function, so ignore it. */
FOR indx IN 2 .. utl_call_stack.dynamic_depth
LOOP
l_return :=
l_return
|| case when l_return is not null then CHR (10) end
|| LPAD (TO_CHAR (utl_call_stack.unit_line (indx)), 8)
|| ' '
|| utl_call_stack.owner (indx)
|| '.'
|| utl_call_stack.concatenate_subprogram (
utl_call_stack.subprogram (indx));
END LOOP;
RETURN l_return;
END;
FUNCTION format_error_stack
RETURN VARCHAR2
IS
l_return VARCHAR2 (32767);
BEGIN
FOR indx IN 1 .. utl_call_stack.error_depth
LOOP
l_return :=
l_return
|| case when l_return is not null then CHR (10) end
|| 'ORA-'
|| LPAD (TO_CHAR (utl_call_stack.error_number (indx)), 5, '0')
|| ': '
|| utl_call_stack.error_msg (indx);
END LOOP;
RETURN l_return;
END;
FUNCTION format_error_backtrace
RETURN VARCHAR2
IS
l_return VARCHAR2 (32767);
BEGIN
FOR indx IN 1 .. utl_call_stack.backtrace_depth
LOOP
l_return :=
l_return
|| case when l_return is not null then CHR (10) end
|| indx
|| ' -> '
|| utl_call_stack.backtrace_unit (indx)
|| ' - Line '
|| TO_CHAR (utl_call_stack.backtrace_line (indx));
END LOOP;
RETURN l_return;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -64610
THEN
/* ORA-64610: bad depth indicator */
RETURN l_return;
ELSE
RAISE;
END IF;
END;
FUNCTION backtrace_to
RETURN VARCHAR2
IS
BEGIN
RETURN utl_call_stack.backtrace_unit (1)
|| ' on line '
|| utl_call_stack.backtrace_line (1);
END;
END;Package Body created.
SET SERVEROUTPUT ONUnsupported Command
CREATE OR REPLACE PROCEDURE p1
IS
PROCEDURE nested_in_p1
IS
BEGIN
DBMS_OUTPUT.put_line ('Call Stack from DBMS_UTILITY');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line ('Call Stack from UTL_CALL_STACK');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line (my_utl_call_stack.format_call_stack);
RAISE NO_DATA_FOUND;
END;
BEGIN
nested_in_p1;
END;Procedure created.
CREATE OR REPLACE PACKAGE pkg
AUTHID DEFINER
IS
PROCEDURE p;
END;Package created.
CREATE OR REPLACE PACKAGE BODY pkg
IS
PROCEDURE p
IS
BEGIN
p1;
END;
END;Package Body created.
CREATE OR REPLACE PROCEDURE p2
AUTHID DEFINER
IS
BEGIN
pkg.p;
END;Procedure created.
CREATE OR REPLACE PROCEDURE p3
AUTHID DEFINER
IS
BEGIN
p2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line ('Error Stack from DBMS_UTILTY');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line ('Error Stack from UTL_CALL_STACK');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line (my_utl_call_stack.format_error_stack);
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line ('Backtrace from DBMS_UTILITY');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line ('Backtrace from UTL_CALL_STACK');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line (my_utl_call_stack.format_error_backtrace);
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line ('Backtrace to: ' || my_utl_call_stack.backtrace_to());
RAISE;
END;Procedure created.
BEGIN
p3;
/* Trapping the exception because if I do not, LiveSQL will not
show the contents of the DBMS_OUTPUT buffer.
*/
EXCEPTION WHEN OTHERS
THEN
NULL;
END;Call Stack from DBMS_UTILITY
-
----- PL/SQL Call Stack ----- object line frame object handle number size name 0x17e70b8c0 8 128 procedure SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1 0x17e70b8c0 16 24 procedure SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1 0x23e3add08 6 24 package body SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.PKG.P 0x6308234a0 5 24 procedure SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P2 0x627a263c8 5 232 procedure SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P3 0x61538c958 2 24 anonymous block 0x966e2b040 1721 88 package body SYS.DBMS_SQL.EXECUTE 0x9ee74f068 1368 4848 package body LIVESQL.ORACLE_SQL_EXEC.RUN_BLOCK 0x9ee74f068 1462 960 package body LIVESQL.ORACLE_SQL_EXEC.RUN_SQL 0x9ee74f068 1577 464 package body LIVESQL.ORACLE_SQL_EXEC.RUN_A_STATEMENT 0x9ee74f068 1902 5008 package body LIVESQL.ORACLE_SQL_EXEC.RUN_STATEMENTS 0x9ee74f068 2019 2528 package body LIVESQL.ORACLE_SQL_EXEC.RUN_STMTS 0x9f677ec20 2320 3712 package body LIVESQL.ORACLE_SQL_SCHEMA.RUN_SAVED_SESSION 0x9e66af9d0 341 112 package body LIVESQL.ORACLE_SQL_SCHEMA_PUB.RUN_SAVED_SESSION 0x219e48568 22 1616 anonymous block 0x966e2b040 1721 88 package body SYS.DBMS_SQL.EXECUTE 0x96c91ada8 1880 936 package body APEX_050100.WWV_FLOW_DYNAMIC_EXEC.RUN_BLOCK5 0x96c91ada8 936 168 package body APEX_050100.WWV_FLOW_DYNAMIC_EXEC.EXECUTE_PLSQL_CODE 0x98cf1b230 71 256 package body APEX_050100.WWV_FLOW_PROCESS_NATIVE.PLSQL 0x98cf1b230 1132 4544 package body APEX_050100.WWV_FLOW_PROCESS_NATIVE.EXECUTE_PROCESS 0x9eea33bd0 2399 2744 package body APEX_050100.WWV_FLOW_PLUGIN.EXECUTE_PROCESS 0x9f6794bf8 200 2376 package body APEX_050100.WWV_FLOW_PROCES
-
Call Stack from UTL_CALL_STACK
-
----- PL/SQL Call Stack ------- Line Object name -------- ---------------------- 12 SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1.NESTED_IN_P1 16 SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1 6 SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.PKG.P 5 SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P2 5 SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P3 2 .__anonymous_block 1721 SYS.DBMS_SQL.EXECUTE 1368 LIVESQL.ORACLE_SQL_EXEC.RUN_BLOCK 1462 LIVESQL.ORACLE_SQL_EXEC.RUN_SQL 1577 LIVESQL.ORACLE_SQL_EXEC.RUN_A_STATEMENT 1902 LIVESQL.ORACLE_SQL_EXEC.RUN_STATEMENTS 2019 LIVESQL.ORACLE_SQL_EXEC.RUN_STMTS 2320 LIVESQL.ORACLE_SQL_SCHEMA.RUN_SAVED_SESSION 341 LIVESQL.ORACLE_SQL_SCHEMA_PUB.RUN_SAVED_SESSION 22 .__anonymous_block 1721 SYS.DBMS_SQL.EXECUTE 1880 APEX_050100.WWV_FLOW_DYNAMIC_EXEC.RUN_BLOCK5 936 APEX_050100.WWV_FLOW_DYNAMIC_EXEC.EXECUTE_PLSQL_CODE 71 APEX_050100.WWV_FLOW_PROCESS_NATIVE.PLSQL 1132 APEX_050100.WWV_FLOW_PROCESS_NATIVE.EXECUTE_PROCESS 2399 APEX_050100.WWV_FLOW_PLUGIN.EXECUTE_PROCESS 200 APEX_050100.WWV_FLOW_PROCESS.PERFORM_PROCESS 443 APEX_050100.WWV_FLOW_PROCESS.PERFORM 4857 APEX_050100.WWV_FLOW.SHOW.RUN_BEFORE_HEADER_CODE 4904 APEX_050100.WWV_FLOW.SHOW 173 APEX_050100.F 2 .__anonymous_block
-
Error Stack from DBMS_UTILTY
-
ORA-01403: no data found ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1", line 13 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1", line 16 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.PKG", line 6 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P2", line 5
Error Stack from UTL_CALL_STACK
-
ORA-01403: no data found ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1", line 13 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1", line 16 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.PKG", line 6 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P2", line 5
-
Backtrace from DBMS_UTILITY
-
ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1", line 13 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1", line 16 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.PKG", line 6 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P2", line 5 ORA-06512: at "SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P3", line 5
-
Backtrace from UTL_CALL_STACK
-
1 -> SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1 - Line 13 2 -> SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1 - Line 16 3 -> SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.PKG - Line 6 4 -> SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P2 - Line 5 5 -> SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P3 - Line 5
-
Backtrace to: SQL_GCJKCMOGHRVJQVQBGWWOVMCLH.P1 on line 13