FORALL and SAVE EXCEPTIONS: Bulk DML That Does Not Stop on First Error

The Power and the Problem With FORALL

The FORALL statement is the counterpart to BULK COLLECT on the write side of bulk processing. It sends an entire collection of DML operations to the SQL engine in a single context switch, delivering massive performance improvements over row by row processing. However, by default, if any single DML operation in the batch fails, the entire FORALL statement stops and raises an exception. All successful operations within that FORALL batch are rolled back.

In many real world scenarios, you need a different behavior. When processing a batch of 10,000 records, you want to successfully process the 9,990 valid ones and capture the 10 failures for later review. This is exactly what SAVE EXCEPTIONS provides.

How SAVE EXCEPTIONS Works

DECLARE
   TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
   TYPE t_salaries IS TABLE OF employees.salary%TYPE;

   l_emp_ids  t_emp_ids  := t_emp_ids(100, 101, 102, 999, 103, 998);
   l_salaries t_salaries := t_salaries(5000, 6000, 7000, 8000, 9000, 10000);

   l_error_count PLS_INTEGER;
   bulk_errors   EXCEPTION;
   PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
   FORALL i IN 1 .. l_emp_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees
         SET salary = l_salaries(i)
       WHERE employee_id = l_emp_ids(i);

EXCEPTION
   WHEN bulk_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.PUT_LINE(l_error_count || ' errors occurred:');

      FOR j IN 1 .. l_error_count LOOP
         DBMS_OUTPUT.PUT_LINE(
            'Index: ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
            ' Error Code: ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE ||
            ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
         );
      END LOOP;

      -- Commit the successful operations
      COMMIT;
END;
/

Understanding SQL%BULK_EXCEPTIONS

After a FORALL with SAVE EXCEPTIONS completes (or rather, after it raises the ORA-24381 exception), the SQL%BULK_EXCEPTIONS collection contains one entry for each failed operation. Each entry has two fields: ERROR_INDEX (the position in the collection where the error occurred) and ERROR_CODE (the Oracle error number, without the negative sign). You can use these to identify which records failed and why, log the failures, and then commit all the successful operations.

Production Pattern: Process, Log Failures, Commit Successes

PROCEDURE process_batch(p_batch_id IN NUMBER)
IS
   CURSOR c_records IS
      SELECT record_id, data_value
        FROM staging_table
       WHERE batch_id = p_batch_id
         AND status = 'PENDING';

   TYPE t_records IS TABLE OF c_records%ROWTYPE;
   l_records t_records;

   bulk_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
   OPEN c_records;
   LOOP
      FETCH c_records BULK COLLECT INTO l_records LIMIT 5000;
      EXIT WHEN l_records.COUNT = 0;

      BEGIN
         FORALL i IN 1 .. l_records.COUNT SAVE EXCEPTIONS
            INSERT INTO target_table(record_id, processed_value)
            VALUES (l_records(i).record_id, transform(l_records(i).data_value));
      EXCEPTION
         WHEN bulk_errors THEN
            -- Log each failure
            FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
               INSERT INTO processing_errors(
                  batch_id, record_id, error_code, error_message
               ) VALUES (
                  p_batch_id,
                  l_records(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).record_id,
                  SQL%BULK_EXCEPTIONS(j).ERROR_CODE,
                  SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
               );
            END LOOP;
      END;

      COMMIT;
   END LOOP;
   CLOSE c_records;
END process_batch;

LOG ERRORS Alternative

Oracle also provides the LOG ERRORS clause for DML statements, which can capture failed rows into an error table without interrupting the DML operation. However, LOG ERRORS works only with regular DML statements, not with FORALL. The SAVE EXCEPTIONS approach gives you more control over error handling and is the preferred pattern for FORALL based bulk processing.

Version Notes

FORALL with SAVE EXCEPTIONS has been available since Oracle 9i. The behavior has been stable across all subsequent versions including 19c through 23ai. One minor improvement in Oracle 12c was better memory management for the SQL%BULK_EXCEPTIONS collection when processing very large batches with many errors. In all current versions, the pattern shown above is the standard best practice for fault tolerant bulk DML.