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

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.