# Deep Dive — SAVE EXCEPTIONS

> One bad row out of 10,000 should cost you one row — not the batch.

Used in: [Chapter 2 — Bulk](../course-plan.md), [Chapter 7 — Exceptions](../course-plan.md) · Appendix entry: [SAVE EXCEPTIONS](../appendix.md#save-exceptions) · Related: [FORALL](forall.md), [PRAGMA EXCEPTION_INIT](pragma-exception_init.md), [EXCEPTION](exception.md), [MERGE](merge.md) (`LOG ERRORS`)

---

## 1. What It Is and When

Without `SAVE EXCEPTIONS`, a `FORALL` is atomic: the first element that fails raises immediately, and every element already processed in that `FORALL` is rolled back. With it, the SQL engine keeps going, records each failure, and raises **one** exception at the end — `ORA-24381: error(s) in array DML` — with the details in `SQL%BULK_EXCEPTIONS`.

Use it in every `FORALL` that processes data you do not fully control (feeds, user input, anything with constraints that can fail). Skip it when a single failure genuinely must abort the batch.

## 2. Syntax

```sql
FORALL i IN ... SAVE EXCEPTIONS <dml>;

-- afterwards, in the handler for ORA-24381:
SQL%BULK_EXCEPTIONS.COUNT              -- number of failed iterations
SQL%BULK_EXCEPTIONS(j).ERROR_INDEX     -- which iteration failed
SQL%BULK_EXCEPTIONS(j).ERROR_CODE      -- the ORA error number, as a POSITIVE integer
```

There is no message stored — `SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)` gives the generic text.

## 3. Examples on the Course Schema

### 3a. The basic shape

```sql
DECLARE
  TYPE t_rows IS TABLE OF orders%ROWTYPE;
  l_rows t_rows;
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  SELECT * BULK COLLECT INTO l_rows FROM orders_stage;        -- a feed: some rows are bad

  FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
    INSERT INTO orders VALUES l_rows(i);

  COMMIT;
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      log_error('orders load, stage row ' || l_rows(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).order_id,
                -SQL%BULK_EXCEPTIONS(j).ERROR_CODE,
                SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
    COMMIT;      -- the successful rows are still pending; decide explicitly
END;
```

Note the `COMMIT` in the handler: `ORA-24381` does **not** roll back the rows that succeeded. They sit uncommitted until you commit or roll back — the handler must choose.

### 3b. Retry the failures one at a time to get the real message

`SQL%BULK_EXCEPTIONS` gives you a code, not "which constraint, which value". Re-executing the failed rows individually gets the full `SQLERRM`:

```sql
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DECLARE
        k PLS_INTEGER := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
      BEGIN
        INSERT INTO orders VALUES l_rows(k);                 -- single row: full error text
      EXCEPTION
        WHEN OTHERS THEN
          log_error('orders load, order_id ' || l_rows(k).order_id, SQLCODE, SQLERRM);
      END;
    END LOOP;
    COMMIT;
```

The retry costs one round trip per failed row — fine, failures are the minority.

### 3c. Collect the failed subscripts for a second pass — VALUES OF

```sql
DECLARE
  TYPE t_idx IS TABLE OF PLS_INTEGER;
  l_failed t_idx := t_idx();
BEGIN
  BEGIN
    FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
      UPDATE orders SET amount = l_rows(i).amount WHERE order_id = l_rows(i).order_id;
  EXCEPTION
    WHEN e_bulk_errors THEN
      FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        l_failed.EXTEND;
        l_failed(l_failed.COUNT) := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
      END LOOP;
  END;

  -- a different treatment for the failed subset
  FORALL i IN VALUES OF l_failed
    INSERT INTO orders_quarantine (order_id, amount, reason)
    VALUES (l_rows(i).order_id, l_rows(i).amount, 'reprice failed');
END;
```

### 3d. INDICES OF and ERROR_INDEX — the mapping caveat

With `FORALL i IN 1..n`, `ERROR_INDEX` is the subscript. With `INDICES OF` / `VALUES OF` on a sparse collection, `ERROR_INDEX` is the **iteration number** (1st, 2nd, 3rd element visited), not the subscript. Map it back:

```sql
FUNCTION nth_subscript (p_coll t_rows, p_n PLS_INTEGER) RETURN PLS_INTEGER IS
  k PLS_INTEGER := p_coll.FIRST;
BEGIN
  FOR n IN 2 .. p_n LOOP k := p_coll.NEXT(k); END LOOP;
  RETURN k;
END;
```

Or avoid the problem: compact the collection into a dense one before the `FORALL`.

### 3e. The set-based alternative — LOG ERRORS

When the source is a table and the DML is a single statement, `LOG ERRORS` does the same job without PL/SQL — and it stores the **whole bad row** plus the full message:

```sql
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('ORDERS');

INSERT INTO orders SELECT * FROM orders_stage
LOG ERRORS INTO err$_orders ('nightly load') REJECT LIMIT UNLIMITED;

SELECT ora_err_number$, ora_err_mesg$, order_id, amount FROM err$_orders;
```

`SAVE EXCEPTIONS` is for when the rows were computed in PL/SQL; `LOG ERRORS` is for set-based DML.

## 4. What Breaks at Scale

- Without `SAVE EXCEPTIONS`, a feed with one bad row in every 10,000 fails **every batch**, each time rolling back 9,999 good rows and raising. The job never finishes.
- The error scope is the batch. With `LIMIT 10000` you learn about failures 10,000 rows at a time — early enough to stop a systematically broken feed.
- The handler runs per batch and can be slow if it logs one row per failure through an autonomous transaction ([PRAGMA AUTONOMOUS_TRANSACTION](pragma-autonomous_transaction.md)) — each log is a commit. If failures are numerous, collect them and insert the log in bulk.
- `LOG ERRORS` writes each bad row to the error table conventionally; with `REJECT LIMIT UNLIMITED` and a fully bad feed, that is a full copy of the feed.

## 5. Pitfalls

1. No handler for `-24381` — the exception propagates and looks like a mysterious "error(s) in array DML" to the caller.
2. Treating `ORA-24381` as "the batch failed" and rolling back — you just discarded 9,999 good rows.
3. `ERROR_CODE` is positive; forgetting the minus sign in `SQLERRM(...)` gives "ORA-0001: non-ORACLE exception".
4. `ERROR_INDEX` with `INDICES OF` on a sparse collection — an iteration number, not a subscript.
5. `SQLERRM(-code)` is generic ("unique constraint (.) violated" with no name). For the real message, re-run the row (3b) or use `LOG ERRORS`.
6. `SAVE EXCEPTIONS` does not help with errors raised *before* the DML runs (e.g. a bad bind conversion in PL/SQL) — those are ordinary exceptions.
7. `SQL%BULK_EXCEPTIONS` is overwritten by the next `FORALL` — copy what you need before running another one.

## 6. Related

- [FORALL](forall.md) — the statement it modifies.
- [PRAGMA EXCEPTION_INIT](pragma-exception_init.md) — naming `-24381`.
- [EXCEPTION](exception.md) — handler structure.
- [MERGE](merge.md) / [INSERT SELECT](insert-select.md) — `LOG ERRORS` for set-based DML.