# 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](../course-plan.md) · Appendix entry: [FORMAT_ERROR_BACKTRACE](../appendix.md#format_error_backtrace) · Related: [EXCEPTION](exception.md), [RAISE_APPLICATION_ERROR](raise_application_error.md), [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md)

---

## 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](pragma-autonomous_transaction.md)). 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

1. Calling `FORMAT_ERROR_BACKTRACE` outside a handler — returns NULL.
2. 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.
3. Using `FORMAT_CALL_STACK` to find where the error was — it shows where you *are*, not where it *happened*.
4. `SQLERRM` (512 bytes) instead of `FORMAT_ERROR_STACK` (2000) — long messages and `keep_errors` chains get cut.
5. 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.
6. Wrapped (obfuscated) packages still report line numbers — they refer to the original source lines. Keep the source.
7. 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.

## 6. Related

- [EXCEPTION](exception.md) — where handlers live.
- [RAISE_APPLICATION_ERROR](raise_application_error.md) — `keep_errors` and the error stack.
- [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md) — the log that survives a rollback.