Handling Exceptions in Bulk Operations

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

There are a number of issues regarding exception handling that must be considered when using bulk operations.  In this section, rollback behavior of bulk operations and the methods available to control this behavior is examined.

In order to demonstrate this functionality, a simple test table containing a single mandatory column must first be created.  This is performed using the exception_test.sql script listed below.

exception_test.sql

CREATE TABLE exception_test (
  id  NUMBER(10) NOT NULL
);

After the table is created, the way unhandled exceptions are treated during bulk operations can be examined.

Unhandled Exceptions

Unhandled exceptions during the execution of a bulk operation cause the entire operation to be rolled back.    This functionality is demonstrated using the unhandled_exception.sql script listed below.

unhandled_exception.sql

DECLARE
  TYPE t_tab IS TABLE OF exception_test.id%TYPE;

  l_tab    t_tab := t_tab();
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last) := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50) := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO exception_test
    VALUES (l_tab(i));
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The unhandled_exception.sql script first creates and populates a collection. Next it assigns the value of NULL to the 50th element, thereby forcing an error.  It then truncates the test table, attempts a bulk insert against it and displays the record count.  The output from this script is listed below.

SQL> @unhandled_exception.sql

DECLARE
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")
ORA-06512: at line 18

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
         0

1 row selected.

SQL> SET ECHO OFF

As expected an exception is raised when the bulk operation reaches the 50th element, resulting in the whole operation being rolled back.  The rollback is evident since the record count is zero.

The following section shows the way handled exceptions are treated during bulk operations.

Handled Exceptions

During a bulk operation a savepoint is created between each SQL execution.  In the event a handled exception is raised, the operation is rolled back to the previous savepoint instead of restarting the whole operation.  The handled_exception.sql script listed below demonstrates this behavior.

handled_exception.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test.id%TYPE;

  l_tab    t_tab := t_tab();
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last) := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50) := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last
      INSERT INTO exception_test
      VALUES (l_tab(i));
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLERRM);
  END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The handled_exception.sql script is a modified version of the unhandled_exception.sql script in that the bulk operation has been surrounded by an anonymous block containing an exception handler that displays the error message.  The output from this script is listed below.

SQL> @handled_exception.sql

ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        49

1 row selected.

SQL> SET ECHO OFF

Once again the bulk operation results in an exception, but this time the exception is trapped so the rollback is restricted to the previous savepoint, as shown by the record count of 49.

That works well if the goal is to stop the operation at that point, but what if the goal is to proceed past any problem rows?  In order to achieve this, the SAVE EXCEPTIONS clause of the FORALL statement must be used; the subject of the next section.

Bulk Operations that Complete

Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing.  Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute.  This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – Holds the iteration (not the subscript) of the original FORALL statement that raised the exception.  In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE – Holds the exceptions error code.

The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised.  The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.

save_exceptions.sql

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;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The FORALL statement includes the SAVE EXCEPTIONS clause, and the exception handler displays the number of exceptions and their associated error messages.  The output from the save_exceptions.sql script is listed below.

SQL> @save_exceptions.sql

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> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        98

1 row selected.

SQL> SET ECHO OFF

As expected the test table contains 98 of the 100 records, and the associated error message has been displayed by looping through the SQL%BULK_EXCEPTION collection.

If the SAVE EXCEPTIONS clause is omitted from the FORALL statement, execution of the bulk operation stops at the first exception and the SQL%BULK_EXCEPTIONS collection contains a single record.  The no_save_exceptions.sql script demonstrates this behavior.

no_save_exceptions.sql

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, -01400);
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
      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;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

Notice that in addition to the SAVE EXCEPTIONS clause being removed, the no_save_exceptions.sql script now traps a different error number.  The output from this script is listed below.

SQL> @no_save_exceptions.sql

Number of failures: 1
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into
("TIM_HALL"."EXCEPTION_TEST"."ID")

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        49

1 row selected.

SQL> SET ECHO OFF

As expected there is only a single error in the SQL%BULK_EXCEPTIONS collection, and there are only 49 records in the test table as the operation has rolled back to the preceding implicit savepoint.

As shown from previous examples, a move from conventional operations to bulk operations will require a revision of your current exception handling or the desired results may not appear.

The use of bulk operations with dynamic SQL is explained in the next section.