Errors handling in BULK COLLECT and FORALL using SQL%BULK_EXCEPTIONS

In this article, I will try to cover the most important issue you might face while working with BULK COLLECT and FORALL.

We all know how to use BULK COLLECT and FORALL. Easy, right?

But, so many errors can occur when you are trying to change rows in a table. Constraint violations, values too large for columns, and many more. Now add to that the fact that with FORALL you are executing that DML statement many times over. Managing errors with FORALL is a tricky but important thing to do!

Before diving into the error-related features of FORALL, let’s review some important points about transactions, errors, and exceptions in the world of PL/SQL.

Each SQL statement is atomic (‘all or nothing’). In other words, if your update statement finds 100 rows to change, and as it is changing the 100th of them, if it hits an error, the changes to all 100 rows are reversed.

What does this mean for FORALL? That, by default, the first time the SQL engine encounters an error processing the DML statement passed to it from FORALL, it stops and passes the error back to the PL/SQL engine. No further processing is done, but also any statements completed successfully by the FORALL are still waiting to be committed or rolled back.

You can see this behavior in the code below.

CREATE TABLE bulkcollect_test (c VARCHAR2(2 CHAR))
/

DECLARE
   TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
   l_string   bulkcollect_t := bulkcollect_t ('A', 'AA', 'AAA');
BEGIN
   FORALL indx IN 1 .. l_string.COUNT
      INSERT INTO bulkcollect_test (c)
         VALUES (l_string (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Rows inserted : ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
END;
/

DECLARE
   TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
   l_string   bulkcollect_t := bulkcollect_t ('AAA', 'AA', 'A');
BEGIN
   FORALL indx IN 1 .. l_string.COUNT
      INSERT INTO bulkcollect_test (c)
         VALUES (l_string (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Rows inserted : ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
END;
/

DECLARE
   TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
   l_string   bulkcollect_t := bulkcollect_t ('A', 'AAA', 'AA');
BEGIN
   FORALL indx IN 1 .. l_string.COUNT
      INSERT INTO bulkcollect_test (c)
         VALUES (l_string (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Rows inserted : ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
END;
/

The first block updated 2 rows, the second block updated 0 rows, and the third block updated 1 row.

With FORALL, you are choosing to execute the same statement, many times over. It could be that you do want to stop your FORALL as soon as any statement fails. In which case, you are done. If, however, you want to keep on going, even if there is an SQL error for a particular set of bind variable values, you need to take advantage of the SAVE EXCEPTIONS clause.

Add the SAVE EXCEPTIONS clause to your FORALL statement when you want the PL/SQL runtime engine to execute all DML statements generated by the FORALL, even if one or more than fail with an error.

In the above code, I had some errors. You can’t insert more than 2 characters. But without SAVE EXCEPTIONS we never get past the element where we have more than 2 characters. Only 1 row was inserted when we executed the last block.

Let’s try that again with SAVE EXCEPTIONS.

DECLARE
   TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
   l_string   bulkcollect_t := bulkcollect_t ('A', 'AAA', 'AA');
BEGIN
   FORALL indx IN 1 .. l_string.COUNT SAVE EXCEPTIONS  
      INSERT INTO bulkcollect_test (c)
         VALUES (l_string (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Rows inserted : ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
END;
/

Now, 2 rows have been inserted.

Enroll for our flagship Unlimited Course package

If you want the PL/SQL engine to execute as many of the DML statements as possible, even if errors are raised along the way, add the SAVE EXCEPTIONS clause to the FORALL header. Then, if the SQL engine raises an error, the PL/SQL engine will save that information in a pseudo-collection named SQL%BULK_EXCEPTIONS, and continue executing statements. When all statements have been attempted, PL/SQL then raises the ORA-24381 error.

You can—and should—trap that error in the exception section and then iterate through the contents of SQL%BULK_EXCEPTIONS to find out which errors have occurred. You can then write error information to a log table and/or attempt recovery of the DML statement.

It is a collection of records, each of which has two fields: ERROR_INDEX and ERROR_CODE.

The index field contains a sequentially generated integer, incremented with each statement execution in the SQL engine. For sequentially filled collections, this integer matches the index value of the variable in the bind array. For sparsely-filled collections (see the modules on INDICES OF and VALUES OF for more details), you will have to write special-purpose code to “link back” the nth statement to its index value in the bind array.

ERROR_CODE is the value returned by SQLCODE at the time the error occurred. Note that this collection does not include the error message.

Let’s go back to the same block used to show the effects of SAVE EXCEPTIONS, but now also take advantage of SQL%BULK_EXCEPTIONS.

DECLARE
   TYPE bulkcollect_t IS TABLE OF VARCHAR2(10 CHAR);
   l_string   bulkcollect_t := bulkcollect_t ('A', 'AAA', 'AA');
   l_error_count  NUMBER;
BEGIN
   FORALL indx IN 1 .. l_string.COUNT SAVE EXCEPTIONS  
      INSERT INTO bulkcollect_test (c)
         VALUES (l_string (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Rows inserted : ' || SQL%ROWCOUNT);
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
 FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
               'Error '
            || indx
            || ' occurred on index '
            || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
            || ' attempting to update name to "'
            || l_string (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
            || '"');
         DBMS_OUTPUT.put_line (
               'Oracle error is : '
            || SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
               
      END LOOP;

      ROLLBACK;
END;
/

DROP TABLE bulkcollect_test
/

As expected, the error was trapped successfully.

Leave a Reply

Your email address will not be published. Required fields are marked *