# Deep Dive — EXCEPTION

> An exception handler is a decision: this error is expected and here is what we do, or it is not and it goes up with everything we know about it. `WHEN OTHERS THEN NULL` is the absence of a decision.

Used in: [Chapter 7 — Exceptions](../course-plan.md) · Appendix entry: [EXCEPTION](../appendix.md#exception) · Related: [RAISE_APPLICATION_ERROR](raise_application_error.md), [PRAGMA EXCEPTION_INIT](pragma-exception_init.md), [FORMAT_ERROR_BACKTRACE](format_error_backtrace.md), [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md), [SAVEPOINT](savepoint.md), [SAVE EXCEPTIONS](save-exceptions.md)

---

## 1. What It Is and When

A PL/SQL block has an optional `EXCEPTION` section. When a statement in the block raises, control jumps to the first `WHEN` clause that matches; if none matches, the exception propagates to the enclosing block, then to the caller, and eventually to the client. Handlers are the only place where "what to do about a failure" is decided — so the design question for every handler is: *is this error expected here?*

- **Expected** (a lookup that may find nothing, a duplicate on an idempotent insert, a busy row on `NOWAIT`) → a **specific** handler, with a business decision in it.
- **Unexpected** (everything else) → record what happened (code, message, line, context) and `RAISE` — let the caller decide, all the way up to the job that owns the transaction.

## 2. Syntax

```sql
[<<label>>]
[DECLARE
  <my_exc> EXCEPTION;
  PRAGMA EXCEPTION_INIT(<my_exc>, <-nnnnn>);]
BEGIN
  ...
EXCEPTION
  WHEN <exc1> [OR <exc2> ...] THEN ...
  WHEN OTHERS THEN
    ...
    RAISE;                                 -- re-raise the same exception, same stack
    -- or RAISE <other_exc>; / RAISE_APPLICATION_ERROR(...)
END [label];
```

Inside a handler: `SQLCODE` (negative ORA number; +100 for `NO_DATA_FOUND`; +1 for user-defined), `SQLERRM` (message, up to 512 bytes), `DBMS_UTILITY.FORMAT_ERROR_STACK` / `FORMAT_ERROR_BACKTRACE` ([FORMAT_ERROR_BACKTRACE](format_error_backtrace.md)).

Predefined exceptions worth knowing by name:

| Exception | ORA | Raised by |
| --- | --- | --- |
| `NO_DATA_FOUND` | +100 / -1403 | `SELECT INTO` with no row; missing collection key |
| `TOO_MANY_ROWS` | -1422 | `SELECT INTO` with > 1 row |
| `DUP_VAL_ON_INDEX` | -1 | unique constraint / index violation |
| `VALUE_ERROR` | -6502 | conversion, truncation, `NULL` into `NOT NULL` variable |
| `INVALID_NUMBER` | -1722 | `TO_NUMBER` in SQL context |
| `ZERO_DIVIDE` | -1476 | division by zero |
| `CURSOR_ALREADY_OPEN` / `INVALID_CURSOR` | -6511 / -1001 | cursor misuse |
| `TIMEOUT_ON_RESOURCE` | -51 | lock wait timeout |
| `COLLECTION_IS_NULL` / `SUBSCRIPT_BEYOND_COUNT` / `SUBSCRIPT_OUTSIDE_LIMIT` | -6531 / -6533 / -6532 | collection misuse |
| `ROWTYPE_MISMATCH` | -6504 | ref cursor shape mismatch |
| `STORAGE_ERROR` / `PROGRAM_ERROR` | -6500 / -6501 | memory / internal |
| `ACCESS_INTO_NULL` / `SELF_IS_NULL` | -6530 / -30625 | object types |

Anything else (`ORA-00054`, `ORA-00060`, `ORA-01555`, `ORA-24381`, your `-20xxx`) gets a name via [PRAGMA EXCEPTION_INIT](pragma-exception_init.md).

## 3. Examples on the Course Schema

### 3a. Specific handlers with decisions

```sql
CREATE OR REPLACE FUNCTION customer_tier (p_id NUMBER) RETURN VARCHAR2 IS
  v_tier customers.tier%TYPE;
BEGIN
  SELECT tier INTO v_tier FROM customers WHERE customer_id = p_id;
  RETURN v_tier;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'NONE';                                    -- expected: unknown customer = no tier
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20001, 'duplicate customer_id ' || p_id);   -- data corruption: stop
END;
/
```

No `WHEN OTHERS` at all: anything else (`ORA-01555`, `ORA-04031`, a dropped table) propagates unchanged, with its full stack.

### 3b. The per-row handler done right — inner block, log, continue or stop

```sql
DECLARE
  l_failures PLS_INTEGER := 0;
BEGIN
  FOR r IN (SELECT order_id, amount FROM orders WHERE status = 'NEW') LOOP
    BEGIN
      process_order(r.order_id, r.amount);
    EXCEPTION
      WHEN e_business_rule THEN                       -- named via EXCEPTION_INIT, e.g. -20050
        log_error('process_order ' || r.order_id, SQLCODE, SQLERRM);
        l_failures := l_failures + 1;
        IF l_failures > 1000 THEN
          RAISE_APPLICATION_ERROR(-20099, 'too many failures, aborting');   -- systemic: stop
        END IF;
      -- no WHEN OTHERS: infrastructure errors abort the job immediately
    END;
  END LOOP;
END;
```

Contrast with `WHEN OTHERS THEN NULL` from the chapter: here the *class* of error is chosen, the failure is recorded, and a threshold turns "a few bad rows" into "something is wrong, stop".

### 3c. User-defined exceptions across procedures

```sql
CREATE OR REPLACE PACKAGE order_errors AS
  e_not_found     EXCEPTION;  PRAGMA EXCEPTION_INIT(e_not_found,     -20010);
  e_already_done  EXCEPTION;  PRAGMA EXCEPTION_INIT(e_already_done,  -20011);
  e_invalid_state EXCEPTION;  PRAGMA EXCEPTION_INIT(e_invalid_state, -20012);
END;
/
CREATE OR REPLACE PROCEDURE ship_order (p_order_id NUMBER) IS
  v_status orders.status%TYPE;
BEGIN
  SELECT status INTO v_status FROM orders WHERE order_id = p_order_id FOR UPDATE NOWAIT;
  IF v_status = 'SHIPPED' THEN
    RAISE_APPLICATION_ERROR(-20011, 'order ' || p_order_id || ' already shipped');
  ELSIF v_status <> 'PAID' THEN
    RAISE_APPLICATION_ERROR(-20012, 'order ' || p_order_id || ' is ' || v_status);
  END IF;
  UPDATE orders SET status = 'SHIPPED' WHERE order_id = p_order_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20010, 'order ' || p_order_id || ' not found');
END;
/
-- caller
BEGIN
  ship_order(1001);
EXCEPTION
  WHEN order_errors.e_already_done THEN NULL;                -- idempotent: fine
  WHEN order_errors.e_not_found OR order_errors.e_invalid_state THEN
    log_error('ship_order', SQLCODE, SQLERRM); RAISE;
END;
```

Numbers in a package spec, names everywhere else. Clients see `ORA-20011` with the message; PL/SQL callers catch by name.

### 3d. The exception that isn't raised — NO_DATA_FOUND in SQL context

```sql
CREATE OR REPLACE FUNCTION lookup_tier (p_id NUMBER) RETURN VARCHAR2 IS
  v customers.tier%TYPE;
BEGIN
  SELECT tier INTO v FROM customers WHERE customer_id = p_id;   -- no row → NO_DATA_FOUND
  RETURN v;
END;
/
SELECT lookup_tier(-1) FROM dual;      -- returns NULL. No error. The exception is swallowed by SQL.
BEGIN
  DBMS_OUTPUT.PUT_LINE(lookup_tier(-1));   -- raises ORA-01403 here, in PL/SQL context
END;
```

A function called from SQL that hits `NO_DATA_FOUND` returns NULL silently. Handle it inside the function if NULL is not what you mean.

### 3e. Retry on a transient error

```sql
DECLARE
  e_deadlock EXCEPTION;  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
  e_busy     EXCEPTION;  PRAGMA EXCEPTION_INIT(e_busy, -54);
  l_try PLS_INTEGER := 0;
BEGIN
  LOOP
    BEGIN
      l_try := l_try + 1;
      SAVEPOINT before_try;
      ship_order(1001);
      EXIT;
    EXCEPTION
      WHEN e_deadlock OR e_busy THEN
        ROLLBACK TO before_try;
        IF l_try >= 3 THEN RAISE; END IF;
        DBMS_SESSION.SLEEP(0.5 * l_try);          -- DBMS_LOCK.SLEEP before 18c
    END;
  END LOOP;
END;
```

### 3f. Exceptions in the declaration section

```sql
DECLARE
  v_status VARCHAR2(3) := 'NEWX';       -- VALUE_ERROR raised HERE, during declaration
BEGIN
  NULL;
EXCEPTION
  WHEN VALUE_ERROR THEN NULL;           -- does NOT catch it: handlers cover the BEGIN section only
END;
```

Wrap the block in an outer block if declaration-time errors must be handled.

## 4. What Breaks at Scale

- **Exceptions as control flow per row.** `SELECT INTO` + `NO_DATA_FOUND` handler inside a 500M-row loop: raising an exception costs orders of magnitude more than a boolean test. For membership checks use [EXISTS](exists.md) or an in-memory lookup; for "insert if not exists" use [MERGE](merge.md), not `DUP_VAL_ON_INDEX`.
- **A nested block per row** has a small fixed cost; with `FORALL` it disappears — errors are handled per batch with [SAVE EXCEPTIONS](save-exceptions.md).
- **Logging per failure through an autonomous transaction** is a commit per failure ([PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md)). When failures are the majority, collect and log in bulk.
- **`SQLERRM` truncation.** 512 bytes; nested `ORA-06512` lines get cut off. Use `FORMAT_ERROR_STACK` for the message and `FORMAT_ERROR_BACKTRACE` for the line.

## 5. Pitfalls

1. `WHEN OTHERS THEN NULL` — or `WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(...)`, which is the same thing in a batch job.
2. `WHEN OTHERS` not ending in `RAISE`/`RAISE_APPLICATION_ERROR` — `PLW-06009`; treat the warning as an error.
3. Handler order: the first matching `WHEN` wins; `OTHERS` must be last (and is the only one that can follow a match-everything).
4. Raising a *new* exception in a handler (`RAISE_APPLICATION_ERROR` without `TRUE`) discards the original stack. Log the backtrace first, or pass `keep_errors => TRUE`.
5. `NO_DATA_FOUND` swallowed in SQL context (3d).
6. A user-defined exception without `EXCEPTION_INIT` has `SQLCODE = 1` and the message "User-Defined Exception" — useless outside PL/SQL. Give it a `-20xxx` code.
7. Exceptions inside a cursor `FOR` loop close the cursor — a handler outside the loop cannot resume it. Handle inside the loop if the loop must continue.
8. Handling `ORA-01555` or `ORA-04031` per row and "continuing" — those mean the environment is broken; stop.

## 6. Related

- [RAISE_APPLICATION_ERROR](raise_application_error.md) — business errors with codes.
- [PRAGMA EXCEPTION_INIT](pragma-exception_init.md) — naming ORA errors.
- [FORMAT_ERROR_BACKTRACE](format_error_backtrace.md) — the line number.
- [PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md) — the logging procedure.
- [SAVEPOINT](savepoint.md) — undoing partial work in the handler.
- [SAVE EXCEPTIONS](save-exceptions.md) — per-row errors in bulk DML.