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.

Become an Oracle Database Developer: Master SQL and PL/SQL with our comprehensive course!

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.

Similar Posts

27 Comments

  1. Thanks for sharing superb informations. Your web-site is so cool. I am impressed by the details that you¦ve on this site. It reveals how nicely you understand this subject. Bookmarked this website page, will come back for extra articles. You, my friend, ROCK! I found just the information I already searched everywhere and simply could not come across. What an ideal site.

  2. That is the proper blog for anybody who wants to seek out out about this topic. You understand a lot its almost onerous to argue with you (not that I actually would want…HaHa). You undoubtedly put a brand new spin on a subject thats been written about for years. Nice stuff, just nice!

  3. I think other web site proprietors should take this site as an model, very clean and magnificent user genial style and design, let alone the content. You’re an expert in this topic!

  4. magnificent post, very informative. I wonder why the other specialists of this sector do not notice this. You should continue your writing. I am sure, you have a huge readers’ base already!

  5. I like what you guys are up too. Such clever work and reporting! Carry on the superb works guys I have incorporated you guys to my blogroll. I think it will improve the value of my website 🙂

  6. I’ve read a few good stuff here. Certainly worth bookmarking for revisiting. I wonder how much effort you put to create such a excellent informative website.

  7. Hello There. I found your blog using msn. This is an extremely well written article. I will make sure to bookmark it and return to read more of your useful info. Thanks for the post. I’ll definitely comeback.

  8. Hey there, You have performed an excellent job. I’ll definitely digg it and personally suggest to my friends. I’m confident they will be benefited from this site.

  9. Wonderful website. A lot of helpful information here. I¦m sending it to several pals ans also sharing in delicious. And naturally, thank you in your sweat!

  10. Heya i’m for the first time here. I came across this board and I find It truly useful & it helped me out a lot. I hope to give something back and help others like you helped me.

  11. I found your blog web site on google and examine a number of of your early posts. Proceed to keep up the superb operate. I just additional up your RSS feed to my MSN News Reader. Looking for forward to studying extra from you afterward!…

Leave a Reply

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