CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
DBMS_OUTPUT.put_line ('running proc1');
RAISE NO_DATA_FOUND;
END;
CREATE OR REPLACE PROCEDURE proc2
IS
l_str VARCHAR2 (30) := 'calling proc1';
BEGIN
DBMS_OUTPUT.put_line (l_str);
proc1;
END;
CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
DBMS_OUTPUT.put_line ('calling proc2');
proc2;
END;
Without Back Trace....
BEGIN
DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 unhandled');
proc3;
END;
Trap and Display Error Stack (Error Message)
BEGIN
DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 unhandled');
proc3;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
Add Back Trace to Error Handler
CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
DBMS_OUTPUT.put_line ('calling proc2');
proc2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error backtrace at top level:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
BEGIN
DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace');
proc3;
END;
Re-Raise Exception
CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
DBMS_OUTPUT.put_line ('running proc1');
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error backtrace in block where raised:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
Can't Trace All the Way Back
CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
DBMS_OUTPUT.put_line ('calling proc2');
proc2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error backtrace at top level:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
BEGIN
DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1, re-reraise in Proc1');
proc3;
END;
Handle and Raise At Every Level
CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
DBMS_OUTPUT.put_line ('running proc1');
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error stack in block where raised:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
CREATE OR REPLACE PROCEDURE proc2
IS
BEGIN
DBMS_OUTPUT.put_line ('calling proc1');
proc1;
EXCEPTION
WHEN OTHERS
THEN
RAISE VALUE_ERROR;
END;
CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
DBMS_OUTPUT.put_line ('calling proc2');
proc2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error backtrace at top level:');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
BEGIN
DBMS_OUTPUT.put_line
('Proc3 -> Proc2 -> Proc1, re-reraise in Proc1, raise VE in Proc2');
proc3;
END;