Deep Dive — EXCEPTION

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

[<<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).

Predefined exceptions worth knowing by name:

Anything else (ORA-00054, ORA-00060, ORA-01555, ORA-24381, your -20xxx) gets a name via PRAGMA EXCEPTION_INIT.

3. Examples on the Course Schema

3a. Specific handlers with decisions

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

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

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

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

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

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 or an in-memory lookup; for "insert if not exists" use MERGE, 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.
• Logging per failure through an autonomous transaction is a commit per failure (PRAGMA AUTONOMOUS_TRANSACTION). 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 — business errors with codes.
• PRAGMA EXCEPTION_INIT — naming ORA errors.
• FORMAT_ERROR_BACKTRACE — the line number.
• PRAGMA AUTONOMOUS_TRANSACTION — the logging procedure.
• SAVEPOINT — undoing partial work in the handler.
• SAVE EXCEPTIONS — per-row errors in bulk DML.