Deep Dive — SAVE EXCEPTIONS

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

Used in
Chapter 2 — Bulk Chapter 7 — Exceptions
Appendix entry
SAVE EXCEPTIONS
Related
FORALL PRAGMA EXCEPTION_INIT EXCEPTION MERGE (LOG ERRORS)

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.

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.

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.

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.

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;

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.

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.

  • 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) — 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.

  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.