PL/SQL Bulk Processing Without Losing Failed Rows: BULK COLLECT, FORALL and SAVE EXCEPTIONS

A nightly interface can process thousands of records successfully and still be operationally unreliable. One invalid value stops the load, a restart repeats rows already handled, or the log records only ORA-24381 without identifying the source record. These are reasons to design the batch boundary, error capture and transaction policy together.

Oracle provides two complementary mechanisms: BULK COLLECT fetches rows into collections, and FORALL binds collections to repeated DML operations. They reduce context switching between PL/SQL and SQL, but do not replace good SQL design. This article uses an illustrative staging-to-target interface and focuses on decisions that matter during production support.

Start by asking whether PL/SQL is necessary

If every eligible source row can be transformed and inserted with one INSERT INTO ... SELECT statement, try that first. A set-based statement is often simpler to reason about and avoids an extra fetch into PL/SQL memory. Bulk PL/SQL becomes more useful when the application requires per-record procedural validation, complex mapping, or row-level error correlation that cannot be handled cleanly by a single SQL statement.

Do not assume that FORALL makes poorly selective queries fast. Examine execution plans, indexes, constraints and row counts before optimizing context switches. Oracle documents bulk binding in its PL/SQL bulk SQL reference.

Design the interface around identifiable source rows

Imagine a table called demo_cost_stage containing stage_id, cost_amount and process_status. Its target is demo_cost_target, with stage_id as a unique processing key. Both names and the following code are illustrative; create and test the schema in a nonproduction database before adapting them.

A durable interface needs a stable source identifier. The key lets you explain which row failed, mark which rows completed and avoid accidental duplicate inserts after a retry. An integration instance ID can group a run, but should not replace the source record key.

Fetch bounded batches with BULK COLLECT LIMIT

A plain SELECT BULK COLLECT INTO can populate an entire collection. For a large extract, that can consume unnecessary session memory. A cursor with FETCH ... BULK COLLECT INTO ... LIMIT provides a controllable batch size. The number 500 below is a starting point for testing, not a universal optimum.

DECLARE
  CURSOR c_stage IS
    SELECT stage_id, cost_amount
      FROM demo_cost_stage
     WHERE process_status = 'N'
     ORDER BY stage_id;
  TYPE t_rows IS TABLE OF c_stage%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c_stage;
  LOOP
    FETCH c_stage BULK COLLECT INTO l_rows LIMIT 500;
    EXIT WHEN l_rows.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('Fetched ' || l_rows.COUNT);
    -- Validate and process this batch here.
  END LOOP;
  CLOSE c_stage;
END;
/

The EXIT condition matters: the final fetch may contain fewer rows than LIMIT, and an empty fetch marks completion. Benchmark several batch sizes using realistic data and observe memory, elapsed time, undo and logging overhead. A larger collection is not automatically faster.

Use FORALL when the DML is uniform

FORALL is not a general-purpose loop containing arbitrary procedural statements. It executes one DML statement repeatedly using collection values. A unique constraint on the target stage_id is a useful defense against duplicate processing, but it also means duplicate input keys must be handled intentionally.

FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
  INSERT INTO demo_cost_target (stage_id, cost_amount)
  VALUES (l_rows(i).stage_id, l_rows(i).cost_amount);

SAVE EXCEPTIONS changes failure behavior. Rather than stopping at the first failed DML execution, Oracle attempts remaining executions and then raises ORA-24381 if any failed. Successful executions in that FORALL are not automatically committed. The caller must decide whether to preserve or roll them back. See Oracle's optimization and tuning documentation.

Capture the actual failing source keys

The following pattern handles ORA-24381 and associates each failed execution with its source stage_id. It assumes a dense collection populated by FETCH, with FORALL indices from 1 through COUNT. Sparse collections using INDICES OF or VALUES OF require more careful index mapping.

DECLARE
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
  l_failed_index PLS_INTEGER;
BEGIN
  FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
    INSERT INTO demo_cost_target (stage_id, cost_amount)
    VALUES (l_rows(i).stage_id, l_rows(i).cost_amount);
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      l_failed_index := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
      DBMS_OUTPUT.PUT_LINE(
        'stage_id=' || l_rows(l_failed_index).stage_id ||
        ', error=' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE
      );
    END LOOP;
    -- Persist errors and choose transaction outcome.
END;

This is an error-capture fragment, not a complete restartable loader. DBMS_OUTPUT is convenient for demonstration but is not a durable operational audit. Store the source key, error code, run ID and relevant diagnostics in an error table or existing interface logging facility. SQLERRM with the negative ERROR_CODE can provide a message, although Oracle notes substitution arguments may be omitted.

Preserve SQL%BULK_EXCEPTIONS and SQL%BULK_ROWCOUNT information immediately after the FORALL if your logic depends on it. Oracle's bulk SQL reference explains ERROR_INDEX and ERROR_CODE semantics.

Decide what happens to successful rows

There are two legitimate approaches. In an all-or-nothing load, any rejected record makes the batch fail; roll back the entire transaction and correct the source before retrying. In a partial-success load, accepted rows may be committed while rejected rows remain available for correction. This requires accurate row statuses and durable error records.

For partial success, avoid marking every fetched stage row as processed after FORALL. Track failed collection indices and update only confirmed successful source keys. Consider what happens if the target insert succeeds but the stage-status update fails. These actions should normally be in the same transaction so a rollback does not leave source and target disagreeing.

Committing every row usually increases overhead and makes recovery harder. Committing per validated batch can be reasonable, but it changes atomicity. Batch size and commit frequency are separate choices. For transaction behavior, review Oracle's exception documentation.

Make retries safe, not merely possible

Restartability requires more than a WHERE process_status = 'N' filter. A job might fail after inserting target rows but before updating staging status, or two workers might select the same pending rows. A target uniqueness rule and carefully designed claim or locking strategy reduce these risks. The right approach depends on concurrency, isolation and whether processing can be partitioned by source key or batch.

• Duplicate input: decide whether to reject, update or treat an existing target key as already completed.
• Validation failures: distinguish source-data problems from infrastructure failures so operators know whether retrying is useful.
• Run observability: record selected, inserted, rejected and retried counts, then reconcile against stage and target tables.
• Failure injection: test duplicate keys, oversize values, unexpected exceptions and restart after rollback.

For very high volumes, evaluate whether a set-based load with database error logging, partition-aware processing or dedicated bulk-loading mechanism is more appropriate. Bulk PL/SQL is one option, not the default answer to every data movement problem.

Production troubleshooting sequence

1. Confirm run ID, stage selection predicate and source row count.
2. Check whether failures are data-specific, such as constraints or conversion, or systemic, such as locking.
3. Inspect captured stage IDs and Oracle error codes rather than relying only on aggregate ORA-24381.
4. Compare target rows and stage statuses within the intended transaction boundary.
5. Reproduce the failed batch in a controlled environment before changing LIMIT or commit frequency.
6. Retry only records whose state and business rules permit a safe retry.

The practical takeaway

Good bulk processing is not measured only by happy-path speed. It should explain exactly which records succeeded, which failed, why they failed and what a rerun will do. Use set-based SQL where possible, bounded BULK COLLECT where procedural processing is justified, FORALL for uniform DML, and SAVE EXCEPTIONS when the business accepts record-level failures. Build transaction handling and reconciliation around those choices.

Before deploying, test the illustrative code against your constraints, Oracle database version, transaction policy and interface recovery requirements. The documentation describes database features; it cannot decide the correct partial-success policy for your business process.