create or replace PROCEDURE P_HANDLEERROR
AS
v_msg VARCHAR2(1000);
BEGIN
v_msg := trim(replace(SQLERRM(SQLCODE),'ORA'||SQLCODE||':',null));
--v_msg := SQLERRM(SQLCODE);
-- backtrace;
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK());
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise_application_error(-20999,v_msg);
END P_HANDLEERROR;
/
create or replace PROCEDURE p1 AS
v_x PLS_INTEGER;
BEGIN
p2;
EXCEPTION
WHEN OTHERS THEN
P_HANDLEERROR;
END;
/
create or replace PROCEDURE p2 AS
v_x PLS_INTEGER;
BEGIN
p3;
EXCEPTION
WHEN OTHERS THEN
P_HANDLEERROR;
END;
/
create or replace PROCEDURE p3 AS
v_x PLS_INTEGER;
BEGIN
v_x := 1 / 0;
EXCEPTION
WHEN OTHERS THEN
P_HANDLEERROR;
END;
/
--
-- Final Call
--
set serveroutput on size unlimited
BEGIN
p1;
END;
/
On SQL Developer, I get the below output -
ORA-01476: divisor is equal to zero
ORA-06512: at "VORTEX_BUSS_OWNER.P3", line 4
ORA-20999: ORA-01476: divisor is equal to zero
ORA-06512: at "VORTEX_BUSS_OWNER.P_HANDLEERROR", line 11
ORA-06512: at "VORTEX_BUSS_OWNER.P3", line 7
ORA-06512: at "VORTEX_BUSS_OWNER.P_HANDLEERROR", line 11
ORA-06512: at "VORTEX_BUSS_OWNER.P3", line 7
ORA-06512: at "VORTEX_BUSS_OWNER.P2", line 4
ORA-20999: ORA-01476: divisor is equal to zero
ORA-06512: at "VORTEX_BUSS_OWNER.P_HANDLEERROR", line 11
ORA-06512: at "VORTEX_BUSS_OWNER.P2", line 8
ORA-06512: at "VORTEX_BUSS_OWNER.P_HANDLEERROR", line 11
ORA-06512: at "VORTEX_BUSS_OWNER.P2", line 8
ORA-06512: at "VORTEX_BUSS_OWNER.P1", line 4
Error starting at line : 2 in command -
BEGIN
p1;
END;
Error report -
ORA-20999: ORA-01476: divisor is equal to zero
ORA-06512: at "VORTEX_BUSS_OWNER.P_HANDLEERROR", line 11
ORA-06512: at "VORTEX_BUSS_OWNER.P1", line 8
ORA-06512: at line 2