Deep Dive — PRAGMA EXCEPTION_INIT

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

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:

3. Examples on the Course Schema

3a. A busy row with NOWAIT

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

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

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

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

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 §3b for the full pattern with constants.

3f. Without the pragma — what you are avoiding

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 §3b).
7. Handling -1555 or -4031 per row "and continuing" — naming an error does not make it recoverable.

6. Related

• EXCEPTION — the handler section.
• RAISE_APPLICATION_ERROR — the -20xxx side.
• SAVE EXCEPTIONS — -24381.
• FOR UPDATE — -54, -30006, -60.