Deep Dive — FORMAT_ERROR_BACKTRACE

SQLERRM tells you what went wrong. Only the backtrace tells you where — and it tells you only if you ask before anyone re-raises.

Used in: Chapter 7 — Exceptions
Appendix entry: FORMAT_ERROR_BACKTRACE
Related: EXCEPTION RAISE_APPLICATION_ERROR PRAGMA AUTONOMOUS_TRANSACTION

1. What It Is and When

Three functions in DBMS_UTILITY, all meant to be called inside an exception handler:

Function	Returns	Answers
`FORMAT_ERROR_STACK`	the error message(s), up to 2000 bytes	what — same as `SQLERRM` but longer, and shows the chain when `keep_errors` was used
`FORMAT_ERROR_BACKTRACE`	`ORA-06512: at "SCHEMA.UNIT", line N` per frame from the raise point to the handler	where it was raised
`FORMAT_CALL_STACK`	the current call chain (works anywhere, not only in handlers)	who called whom to get here

From 12c, UTL_CALL_STACK provides the same data as structured values (unit name, line, depth) instead of a string to parse.

Use: FORMAT_ERROR_BACKTRACE in every logging handler. It is the difference between "ORA-01422 somewhere in the nightly batch" and "ORA-01422 at PRICING_PKG line 214".

2. Syntax

SQL

DBMS_UTILITY.FORMAT_ERROR_STACK      RETURN VARCHAR2;
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE  RETURN VARCHAR2;
DBMS_UTILITY.FORMAT_CALL_STACK       RETURN VARCHAR2;

-- 12c+, structured
UTL_CALL_STACK.ERROR_DEPTH                 -- number of errors on the stack
UTL_CALL_STACK.ERROR_NUMBER(i) / ERROR_MSG(i)
UTL_CALL_STACK.BACKTRACE_DEPTH             -- number of backtrace frames
UTL_CALL_STACK.BACKTRACE_UNIT(i) / BACKTRACE_LINE(i)
UTL_CALL_STACK.DYNAMIC_DEPTH               -- call stack depth
UTL_CALL_STACK.SUBPROGRAM(i)               -- qualified name as a collection; CONCATENATE_SUBPROGRAM(...) joins it
UTL_CALL_STACK.OWNER(i) / UNIT_LINE(i) / LEXICAL_DEPTH(i)

-- compile-time context, anywhere
$$PLSQL_UNIT   $$PLSQL_LINE   [$$PLSQL_UNIT_OWNER, $$PLSQL_UNIT_TYPE in 12c+]

3. Examples on the Course Schema

3a. Three levels deep — what each function shows

SQL

CREATE OR REPLACE PACKAGE BODY nightly AS
  PROCEDURE reprice_one (p_id NUMBER) IS
    v NUMBER;
  BEGIN
    SELECT amount INTO v FROM orders WHERE customer_id = p_id;   -- line 5: TOO_MANY_ROWS
  END;
  PROCEDURE reprice IS
  BEGIN
    reprice_one(42);                                             -- line 9
  END;
  PROCEDURE run IS
  BEGIN
    reprice;                                                     -- line 13
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('STACK:     ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      DBMS_OUTPUT.PUT_LINE('BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
      DBMS_OUTPUT.PUT_LINE('CALLSTACK: ' || DBMS_UTILITY.FORMAT_CALL_STACK);
      RAISE;
  END;
END;
/

Output (trimmed):

STACK:     ORA-01422: exact fetch returns more than requested number of rows
BACKTRACE: ORA-06512: at "APP.NIGHTLY", line 5
           ORA-06512: at "APP.NIGHTLY", line 9
           ORA-06512: at "APP.NIGHTLY", line 13
CALLSTACK: ----- PL/SQL Call Stack -----
           object      line  object
           handle    number  name
           0x7f..      16  package body APP.NIGHTLY.RUN
           0x7f..       1  anonymous block

Line 5 is where it happened; line 9 and 13 are how it was reached. The call stack shows where the handler is running (line 16), not where the error was — that is why FORMAT_CALL_STACK is the wrong function for error location.

3b. Re-raising: what survives

SQL

PROCEDURE reprice IS
BEGIN
  reprice_one(42);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;                                              -- (a) backtrace preserved: still "line 5"
    -- RAISE_APPLICATION_ERROR(-20001, 'reprice failed');        -- (b) backtrace now starts HERE
    -- RAISE_APPLICATION_ERROR(-20001, 'reprice failed', TRUE);  -- (c) error stack keeps ORA-01422; backtrace still starts here
END;

RAISE; keeps the original raise point. Anything that raises a new exception resets the backtrace to the new raise point — the original line 5 is gone unless you logged it first. Rule: log the backtrace in the first handler that sees the error, then re-raise however you like.

3c. The logging procedure — capture everything, once

SQL

CREATE OR REPLACE PROCEDURE log_error (p_ctx VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (logged_at, ctx, err_code, err_msg, backtrace, call_stack)
  VALUES (SYSTIMESTAMP, p_ctx, SQLCODE,
          DBMS_UTILITY.FORMAT_ERROR_STACK,        -- full message chain
          DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,    -- where it was raised
          DBMS_UTILITY.FORMAT_CALL_STACK);        -- who called the handler
  COMMIT;
END;
/
-- usage: first handler that catches, before any re-raise
EXCEPTION
  WHEN OTHERS THEN
    log_error('nightly.reprice customer ' || p_id);
    RAISE;

SQLCODE, SQLERRM and the FORMAT_* functions read the current session error state, so they work inside a procedure called from the handler — as long as nothing in between raised or handled another exception. The course's four-argument log_error passes them explicitly; both styles work.

3d. Structured — UTL_CALL_STACK (12c+)

SQL

EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1 .. UTL_CALL_STACK.BACKTRACE_DEPTH LOOP
      DBMS_OUTPUT.PUT_LINE(
        'raised in ' || UTL_CALL_STACK.BACKTRACE_UNIT(i) ||
        ' at line '  || UTL_CALL_STACK.BACKTRACE_LINE(i));
    END LOOP;
    -- the deepest error message without parsing:
    DBMS_OUTPUT.PUT_LINE(UTL_CALL_STACK.ERROR_NUMBER(1) || ': ' || UTL_CALL_STACK.ERROR_MSG(1));
    RAISE;

Frame 1 is the raise point. Unit names are not truncated (the FORMAT_* strings are capped at 2000 bytes).

3e. Cheap context without the stack — $$PLSQL_UNIT / $$PLSQL_LINE

SQL

RAISE_APPLICATION_ERROR(-20010,
  $$PLSQL_UNIT || ':' || $$PLSQL_LINE || ' amount must be positive, got ' || p_amount);
-- ORA-20010: NIGHTLY:41 amount must be positive, got -5

Resolved at compile time; zero runtime cost. Good for messages; the backtrace is still needed for errors you did not raise yourself.

4. What Breaks at Scale

FORMAT_CALL_STACK and UTL_CALL_STACK walk the stack — microseconds, but not free. Call them on the error path only, never per row "just in case".
Logging through an autonomous transaction is a commit per error (PRAGMA AUTONOMOUS_TRANSACTION). When errors are frequent (a bad feed), buffer them in a collection and insert once per batch.
2000-byte limits: a deep backtrace through many packages gets truncated at the far end — the raise point (first line) is always there; the outermost callers may be cut. UTL_CALL_STACK has no such cap.

5. Pitfalls

Calling FORMAT_ERROR_BACKTRACE outside a handler — returns NULL.
Calling it in an outer handler after an inner handler raised a new exception — you get the inner handler's line, not the original error's.
Using FORMAT_CALL_STACK to find where the error was — it shows where you are, not where it happened.
SQLERRM (512 bytes) instead of FORMAT_ERROR_STACK (2000) — long messages and keep_errors chains get cut.
Anonymous blocks show as "anonymous block" with a line number relative to the block — fine in a job, useless from an ad-hoc client. Put code in packages.
Wrapped (obfuscated) packages still report line numbers — they refer to the original source lines. Keep the source.
Expecting the backtrace to include the SQL statement text — it does not. SQLERRM for ORA-00942-type errors names the object; for the statement itself, log it yourself.