# Deep Dive — PRAGMA EXCEPTION_INIT

> `WHEN e_row_locked` reads. `WHEN OTHERS THEN IF SQLCODE = -54` does not — and it also catches everything else on the way.

Used in: [Chapter 7 — Exceptions](../course-plan.md), [Chapter 8 — Locking](../course-plan.md) · Appendix entry: [PRAGMA EXCEPTION_INIT](../appendix.md#pragma-exception_init) · Related: [EXCEPTION](exception.md), [RAISE_APPLICATION_ERROR](raise_application_error.md), [SAVE EXCEPTIONS](save-exceptions.md), [FOR UPDATE](for-update.md)

---

## 1. What It Is and When

Oracle predefines names for about twenty errors (`NO_DATA_FOUND`, `DUP_VAL_ON_INDEX`, …). Every other ORA error — and every `-20xxx` you raise yourself — is anonymous: catchable only by `WHEN OTHERS` and a `SQLCODE` test. `PRAGMA EXCEPTION_INIT` binds a declared exception to an error number so the handler can name it.

Use it for every ORA error your code handles deliberately. A `WHEN OTHERS` that inspects `SQLCODE` is a named handler written the hard way, and it catches the errors you did *not* mean to handle too.

## 2. Syntax

```sql
DECLARE
  <exception_name> EXCEPTION;
  PRAGMA EXCEPTION_INIT(<exception_name>, <error_number>);
```

- The pragma must appear in the **same declarative section**, after the exception declaration.
- `<error_number>` is a literal integer: negative for ORA errors, `100` for `NO_DATA_FOUND` (already named), or your own `-20000..-20999`. No variables, no constants — a literal.
- Predefined exceptions cannot be re-initialized; `-1403`/`+100` is `NO_DATA_FOUND`, `-1` is `DUP_VAL_ON_INDEX`, etc.
- Declared in a **package spec**, the exception is shared by every program that references the package.

Errors worth naming in a large-database job:

| ORA | Meaning | Typical handling |
| --- | --- | --- |
| `-54` | resource busy, `NOWAIT` | retry later / skip |
| `-30006` | resource busy, `WAIT n` timed out | retry / skip |
| `-60` | deadlock detected | roll back to savepoint, retry |
| `-1555` | snapshot too old | abort; shorten the job |
| `-4030` / `-4036` | out of process memory / PGA limit | abort; reduce `LIMIT` |
| `-4031` | shared pool | abort; fix cursor explosion |
| `-2291` / `-2292` | FK parent missing / child records exist | business decision |
| `-1400` | NULL into NOT NULL column | data quality: log |
| `-12899` | value too large for column | data quality: log |
| `-24381` | errors in array DML (`SAVE EXCEPTIONS`) | walk `SQL%BULK_EXCEPTIONS` |
| `-30926` | unstable set of rows in `MERGE` | deduplicate source |
| `-12838` | read after direct-path insert without commit | commit first |
| `-1013` | user requested cancel | stop cleanly |
| `-8177` | serialization failure (`SERIALIZABLE`) | retry |

## 3. Examples on the Course Schema

### 3a. A busy row with NOWAIT

```sql
DECLARE
  e_row_locked EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_row_locked, -54);
  v_id orders.order_id%TYPE;
BEGIN
  SELECT order_id INTO v_id FROM orders WHERE order_id = :id FOR UPDATE NOWAIT;
  process_order(v_id);
EXCEPTION
  WHEN e_row_locked THEN
    DBMS_OUTPUT.PUT_LINE('order ' || :id || ' is being processed elsewhere; skipping');
END;
```

### 3b. Shared names in a package spec

```sql
CREATE OR REPLACE PACKAGE ora_errors AS
  e_row_locked      EXCEPTION;  PRAGMA EXCEPTION_INIT(e_row_locked,      -54);
  e_wait_timeout    EXCEPTION;  PRAGMA EXCEPTION_INIT(e_wait_timeout,    -30006);
  e_deadlock        EXCEPTION;  PRAGMA EXCEPTION_INIT(e_deadlock,        -60);
  e_snapshot_old    EXCEPTION;  PRAGMA EXCEPTION_INIT(e_snapshot_old,    -1555);
  e_bulk_errors     EXCEPTION;  PRAGMA EXCEPTION_INIT(e_bulk_errors,     -24381);
  e_child_exists    EXCEPTION;  PRAGMA EXCEPTION_INIT(e_child_exists,    -2292);
  e_parent_missing  EXCEPTION;  PRAGMA EXCEPTION_INIT(e_parent_missing,  -2291);
END;
/
-- anywhere:
EXCEPTION
  WHEN ora_errors.e_row_locked OR ora_errors.e_wait_timeout THEN ...
```

One place, every job. New name = one line.

### 3c. Deadlock retry

```sql
DECLARE
  l_try PLS_INTEGER := 0;
BEGIN
  LOOP
    BEGIN
      l_try := l_try + 1;
      SAVEPOINT before_try;
      transfer_credit(p_from => 1001, p_to => 1002, p_amount => 50);
      EXIT;
    EXCEPTION
      WHEN ora_errors.e_deadlock THEN
        ROLLBACK TO before_try;
        IF l_try >= 3 THEN RAISE; END IF;
        DBMS_SESSION.SLEEP(0.2 * l_try);
    END;
  END LOOP;
END;
```

On `ORA-00060` Oracle rolls back only the statement that detected the deadlock; the rest of the transaction — and its locks — remain. The `ROLLBACK TO` releases them so the other session can proceed.

### 3d. A friendly message for a FK violation

```sql
CREATE OR REPLACE PROCEDURE delete_customer (p_id NUMBER) IS
BEGIN
  DELETE FROM customers WHERE customer_id = p_id;
EXCEPTION
  WHEN ora_errors.e_child_exists THEN
    RAISE_APPLICATION_ERROR(-20120, 'customer ' || p_id || ' has orders; archive them first', TRUE);
END;
/
```

### 3e. Naming your own -20xxx for callers

```sql
CREATE OR REPLACE PACKAGE order_errors AS
  e_not_found EXCEPTION;  PRAGMA EXCEPTION_INIT(e_not_found, -20010);
END;
/
-- raised somewhere as RAISE_APPLICATION_ERROR(-20010, ...); caught as:
EXCEPTION
  WHEN order_errors.e_not_found THEN ...
```

See [RAISE_APPLICATION_ERROR](raise_application_error.md) §3b for the full pattern with constants.

### 3f. Without the pragma — what you are avoiding

```sql
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -54 THEN
      NULL;                 -- skip busy row
    ELSE
      RAISE;
    END IF;
```

It works. It also means every handler is a `WHEN OTHERS`, every one needs the `ELSE RAISE`, and forgetting the `ELSE` once silently swallows everything. Named exceptions make the mistake impossible to write.

## 4. What Breaks at Scale

- Nothing about the pragma has a runtime cost — the mapping is compile-time. The scale effect is indirect: named handlers let you handle *only* the transient errors (`-54`, `-60`, `-30006`) per row and let the fatal ones (`-1555`, `-4031`) stop the job at once instead of being retried 500M times.
- A retry loop (3c) with no cap or no sleep, under contention, is a busy loop. Cap the attempts; back off.

## 5. Pitfalls

1. Pragma before the exception declaration, or in a different block — `PLS-00701`/`PLS-00702`.
2. A variable or constant as the error number — `PLS-00702`; it must be a literal.
3. Positive numbers other than `100` — not valid ORA codes; `ORA-1403` is `NO_DATA_FOUND` (already named).
4. Two exceptions mapped to the same number in one scope — only one handler can be chosen; ambiguous by design. Do not.
5. Trying to initialize `NO_DATA_FOUND`, `DUP_VAL_ON_INDEX`, … again — `PLS-00703`.
6. Mapping a `-20xxx` in two packages to different meanings — the same number means two things. Allocate ranges ([RAISE_APPLICATION_ERROR](raise_application_error.md) §3b).
7. Handling `-1555` or `-4031` per row "and continuing" — naming an error does not make it recoverable.

## 6. Related

- [EXCEPTION](exception.md) — the handler section.
- [RAISE_APPLICATION_ERROR](raise_application_error.md) — the `-20xxx` side.
- [SAVE EXCEPTIONS](save-exceptions.md) — `-24381`.
- [FOR UPDATE](for-update.md) — `-54`, `-30006`, `-60`.