ORACLE-FORU - How to handle Exception in Bulk Collection Method.

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

We saw how the FORALL syntax 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. 

If there is an exception handler, the work done prior to the exception is kept, but no more processing is done. 

So instead we should use the SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue past them. 

We can subsequently look at the exceptions by referencing the SQL%BULK_EXCEPTION cursor attribute. 

create the following table.

CREATE TABLE exception_test (

  id  NUMBER(10) NOT NULL

);

The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL. 

Since the above table does not allow nulls, these rows will result in an exception. 

The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete. 

In this case, the exception handler just loops through the SQL%BULK_EXCEPTION cursor attribute to see what errors occurred.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();

  l_error_count  NUMBER;

  

  ex_dml_errors EXCEPTION;

  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

BEGIN

  -- Fill the collection.

  FOR i IN 1 .. 100 LOOP

    l_tab.extend;

    l_tab(l_tab.last).id := i;

  END LOOP;

  -- Cause a failure.

  l_tab(50).id := NULL;

  l_tab(51).id := NULL;

  

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.

  BEGIN

    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS

      INSERT INTO exception_test

      VALUES l_tab(i);

  EXCEPTION

    WHEN ex_dml_errors THEN

      l_error_count := SQL%BULK_EXCEPTIONS.count;

      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);

      FOR i IN 1 .. l_error_count LOOP

        DBMS_OUTPUT.put_line('Error: ' || i || 

          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||

          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

      END LOOP;

  END;

END;

/

Number of failures: 2

Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()

Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL>

As expected the errors were trapped. If we query the table we can see that 98 rows were inserted correctly.

SELECT COUNT(*)

FROM   exception_test;

  COUNT(*)

----------

98

1 row selected.

SQL>