Capture child exception details along with parent exception in oracle - Stack Overflow

admin管理员组

文章数量:1122846

I have a procedure where I am explicitly raising exception which needs to get appended with parent exception.

Create or replace procedure 
proc_test_exception
Is
A number;
Begin
A:=1/0;
Exception 
When others then
Raise_application_error(-20090, 'error in child block');
End;

Now I am calling this procedure inside anonymous block

Begin
Process_test_exception;
Exception
When others then
Raise_application_error(-20099,'exception captured in parent block');
End;
/

I need to print all raise application errors which is getting raised from child block along with parent blocks.

Result expected:

Ora-20090 error in child block
Ora-20099 exception captured in parent block

Please help me to achieve this result

I tried to execute above anonymous block, where it is going inside child exception however it is not printing raise application msg from child procedure rather it is printing only parent raise exception message.

I have a procedure where I am explicitly raising exception which needs to get appended with parent exception.

Create or replace procedure 
proc_test_exception
Is
A number;
Begin
A:=1/0;
Exception 
When others then
Raise_application_error(-20090, 'error in child block');
End;

Now I am calling this procedure inside anonymous block

Begin
Process_test_exception;
Exception
When others then
Raise_application_error(-20099,'exception captured in parent block');
End;
/

I need to print all raise application errors which is getting raised from child block along with parent blocks.

Result expected:

Ora-20090 error in child block
Ora-20099 exception captured in parent block

Please help me to achieve this result

I tried to execute above anonymous block, where it is going inside child exception however it is not printing raise application msg from child procedure rather it is printing only parent raise exception message.

• oracle-database

Share Improve this question Follow edited Nov 21, 2024 at 23:04 MT0 168k1111 gold badges6666 silver badges127127 bronze badges asked Nov 21, 2024 at 20:41 Narasimhan MNarasimhan M 922 bronze badges 1

• Are you sure you want to append error messages like this? Oracle's default behavior will propagate exceptions, and include the object name and line number of all errors. That default information would be more than what you are planning to provide. You may not want to catch exceptions at all (unless you are planning to do something else with that data, like log it or take actions based on it.) – Jon Heller Commented Nov 22, 2024 at 2:18

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

When you catch an exception it does not propagate unless you raise it again. Therefore, when you catch an exception using EXCEPTION WHEN OTHERS THEN ... and raise a different exception then the original exception is no longer on the error stack and has been replaced by the new exception.

Having caught it and not re-raised it then the first exception no longer exists.

You either:

• Need to print the exception when you catch it; or
• Can only print one of the two exceptions.

---

For the former option:

BEGIN
  proc_test_exception;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    Raise_application_error(-20099,'exception captured in parent block');
END;
/

Will output to the console:

ORA-20090: error in child block

and raise the exception:

ORA-20099: exception captured in parent block
ORA-06512: at line 6

fiddle

本文标签： Capture child exception details along with parent exception in oracleStack Overflow