How to get the stacktrace for the original exception in oracle PL/SQL from a RAISED exception?

Question

I'm having an issue where the original stack trace gets lost when I catch an exception, then raise it.

1. Exception gets thrown in proc_a
2. Catch the exception.
3. Perform a rollback.
4. RAISE the exception.
5. Catch the exception (parent block)
6. Print Stack Trace: SUBSTR(SQLERRM || chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, 3999)

Example:

DECLARE
BEGIN
   DECLARE
      lv_val VARCHAR2(1);
   BEGIN
      SELECT dummy INTO lv_val -- Line# 6 (desired)
      FROM   dual
      WHERE  dummy = 'FFF';
   EXCEPTION
      WHEN OTHERS THEN
         --DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM || chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, 3999));
         RAISE; -- Line# 12 (actual)
   END;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM || chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, 3999));
END;
/

Desired Result:

The line number for the original exception (step 1).

ORA-01403: no data found
ORA-06512: at line 6

or

ORA-01403: no data found
ORA-06512: at line 12
Caused By:
ORA-01403: no data found
ORA-06512: at line 6

Actual Result:

The line number for the RAISE (Step 4).

ORA-01403: no data found
ORA-06512: at line 12

Additional attempts that failed:

SQLERRM || chr(10) || DBMS_UTILITY.FORMAT_ERROR_STACK()

ORA-01403: no data found
ORA-01403: no data found

SQLERRM || chr(10) || DBMS_UTILITY.FORMAT_CALL_STACK()

ORA-01403: no data found
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xee1cbd68        18  anonymous block

Diaphragm

I don't that is possible. You said already in your yedtion: "when I catch an exception", i.e. the exception is resolved in exception handler. – France

Does this help- #7032873 – Skateboard

Not really. I'm already using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(). And DBMS_UTILITY.FORMAT_ERROR_STACK() only shows the message which is the same for both since it was caught and then raised. And DBMS_UTILITY.FORMAT_CALL_STACK gives the wrong line number, too. – Diaphragm

Once the exception has been catched, I believe you have no way to catch it again; can't you avoid the inner WHEN OTHERS and do the rollback and whatever you need in the external exception handling part? – Caecilian

Not in my particular case since it's a shared utility. – Diaphragm

If there were something that combined DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, that would be perfect since it would contain both stack traces. – Diaphragm

What I sometimes do is check whether dbms_utility.format_error_backtrace already contains sqlerrm before concatenating them. However this is an old problem (and I suspect not unique to PL/SQL - I've hit the same situation in Perl for example). For some notes about the quantum mechanics of exception handling, see mikesmithers.wordpress.com/2015/07/24/…. – Epitaph

I'm a Java guy, so I'm used to good (and nested) stack traces. That article had a lot of good info and examples, though nothing that would show the line number of the original error. – Diaphragm

Answer 1

In your inner exception handler, instead of using the RAISE procdure, use the RAISE_APPLICATION_ERROR procedure passing it the results of the dbms_utility.format_error_backtrace function to get the original line number:

BEGIN
   DECLARE
      lv_val VARCHAR2(1);
   BEGIN
      SELECT dummy INTO lv_val -- Line# 6 (desired)
      FROM   dual
      WHERE  dummy = 'FFF';
   EXCEPTION
      WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20001, dbms_utility.format_error_backtrace,true);
   END;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM || chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, 3999));
END;
/

Without the outer exception handler you'll get the following error report:

Error report -
ORA-20001: ORA-06512: at line 5
ORA-06512: at line 10
ORA-01403: no data found

With outer exception handler you'll get the following:

ORA-20001: ORA-06512: at line 5
ORA-01403: no data found
ORA-06512: at line 10

The message ordering is slightly different, but the info is still all there.

Richmound

This will work for any situation where I have control over how the initial procedure throws the exception. However, since it requires an update to the original proc, it won't be usable if you aren't able to update the original proc. +1 – Diaphragm

You indicated that you were catching then re-raising the exception, so whenever you re-raise the exception just use the RAISE_APPLICATION_ERROR procedure with the third parameter set to true, include the results of the dbms_utility.format_error_backtrace function and any other pertinent info in the second parameter, and a value of your choice between -20000 and -20999 for the first parameter, and the whole error stack will be propagated out. – Richmound