BULK COLLECT - SAVE EXCEPTIONS in Oracle

FORALL allows us to perform bulk DML operations, but what happens if one of those individual operations results in an exception?

If there is no exception handler, all the work done by the current bulk operation is rolled back.So we should use the SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue even error occurred for individual operations results.

SQL> CREATE TABLE BULK_EXCEPTION_TBL (ID NUMBER (10) NOT NULL);

Table created.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     TYPE T_ID IS TABLE OF BULK_EXCEPTION_TBL%ROWTYPE;
  3
  4     L_ID            T_ID := T_ID ();
  5     l_error_count   NUMBER;
  6
  7     E_DML_ERROR     EXCEPTION;
  8     PRAGMA EXCEPTION_INIT (E_DML_ERROR, -24381);
  9  BEGIN
 10     -- Fill the collection.
 11     FOR I IN 1 .. 10
 12     LOOP
 13        L_ID.EXTEND;
 14        L_ID (L_ID.LAST).ID := I;
 15     END LOOP;
 16
 17     -- Cause a failure.
 18     L_ID (5).id := NULL;
 19     L_ID (6).id := NULL;
 20
 21     EXECUTE IMMEDIATE 'TRUNCATE TABLE BULK_EXCEPTION_TBL';
 22
 23     -- Perform a bulk operation.
 24     BEGIN
 25        FORALL i IN L_ID.FIRST .. L_ID.LAST SAVE EXCEPTIONS
 26           INSERT INTO BULK_EXCEPTION_TBL
 27                VALUES L_ID (i);
 28     EXCEPTION
 29        WHEN E_DML_ERROR
 30        THEN
 31           l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
 32           DBMS_OUTPUT.put_line ('Number of failures: ' || l_error_count);
 33
 34           FOR i IN 1 .. l_error_count
 35           LOOP
 36              DBMS_OUTPUT.put_line (
 37                    'Error: '
 38                 || i
 39                 || ' Array Index: '
 40                 || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
 41                 || ' Message: '
 42                 || SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
 43           END LOOP;
 44     END;
 45  END;
 46  /
Number of failures: 2
Error: 1 Array Index: 5 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 6 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL> SELECT * FROM BULK_EXCEPTION_TBL;

        ID
----------
         1
         2
         3
         4
         7
         8
         9
        10

8 rows selected.

SQL>