Feuertip #15: Save those exceptions!

OK, now that we are all experts at forall, let’s dive into the challenge of handling exceptions that occur when you run a forall statement.

First, a note on terminology: the forall is a single PL/SQL statement, not a loop. But embedded within it is a non-query dml statement, as you can see below.

DECLARE
   TYPE ids_t IS TABLE OF employees.department_id%TYPE;
   l_ids ids_t := ids_t (50, 100);
BEGIN
   FORALL l_index IN 1 .. l_ids.COUNT
      DELETE FROM employees
            WHERE department_id = l_ids (l_index);
END;

We do not often talk, in the context of PL/SQL, about statements within statements. So to keep things straight, the phrase “dml statement” will refer to the insert, update or delete within a forall statement.

Generally, we use forall when we want to execute a whole bunch of dml statements in bulk. Not just one update, but 10,000 updates (the same update statement with different bind values).

As soon as any of those statements fail, the forall statement terminates with an exception that corresponds to the SQL error that occurred. Any previously completed dml statements are not rolled back. You decide (or the user decides) what should be done with the results of this partially completed forall statement.

You can see this fact from the script below.

declare
   type namelist_t is table of varchar2 (5000);

   enames_with_errors   namelist_t
      := namelist_t ('ABC'
                   , 'DEF'
                   , rpad ('BIGBIGGERBIGGEST', 1000, 'ABC')
                 , 'GHI'
                    );
begin
   forall indx in 1 .. enames_with_errors.count
      update employees
         set first_name = enames_with_errors (indx);
exception
   when others
   then
      dbms_output.put_line ('Updated ' || sql%rowcount || ' rows.');
      dbms_output.put_line (dbms_utility.format_error_stack);
end;

Updated 214 rows.
ORA-12899: value too large for column "EMPLOYEES"."FIRST_NAME" (actual: 1000, maximum: 20)

“ABC” and “DEF” are valid last names, but that third entry in the collection is way too big. So the forall statement fails on the third dml statement, but SQL%ROWCOUNT shows that 214 rows have been modified. And no attempt was made to update the employees table setting all last names to “GHI.”

That’s the default behavior of forall. But what if that’s not the behavior you want? After all, you are executing a bulk statement. What if you want the PL/SQL engines to at least try to execute every dml statement (or, more accurately, to execute the same dml statement for all the elements in the collection), even if it encounters an error with a particular bind value?

In that case, you want to add the save exceptions clause. let’s run the same code as above, with just one change: add “save exceptions” after the forall statement header.

declare
   type namelist_t is table of varchar2 (5000);

   enames_with_errors   namelist_t
      := namelist_t ('ABC'
                   , 'DEF'
                   , rpad ('BIGBIGGERBIGGEST', 1000, 'ABC')
                 , 'GHI'
                    );
begin
   forall indx in 1 .. enames_with_errors.count save exceptions
      update employees
         set first_name = enames_with_errors (indx);
exception
   when others
   then
      dbms_output.put_line ('Updated ' || sql%rowcount || ' rows.');
      dbms_output.put_line (dbms_utility.format_error_stack);
end;
/

Updated 321 rows.
ORA-24381: error(s) in array DML

Notice that now 321 rows were updated (107 x 3), instead of 214 (107 x 2). And the error is no longer ORA-12899, but instead ORA-24381.

The PL/SQL engine continued past the error it received from the SQL engine from the third bind value. It executed the dml statement for “GHI” and completed that successfully. And then, because there were no more bind variables, the PL/SQL engine raised the ORA-24381 exception to indicate that something had gone wrong.

“Ok,” you might be saying, “that’s great. But how can I tell what went wrong? And since the clause is ‘save exceptions’, where did it save those exceptions?”

When you include save exceptions in your forall header, error information is written out to a pseudo-collection named sql%bulk_exceptions. You can iterate through this collection to find out what went wrong.

Some things to remember about sql%bulk_exceptions:

• It is always filled sequentially from index value 1.
• Each element is a record with two fields: error_index and error_code.
• The error_index is the index value in the bind array for the statement/value that failed.
• The error_code is the value returned by sqlcode for that error. But unsigned (some call it a negative sign).
• It does not store the error message (which means you can lose some information, such as the name of the constraint).
• Things get tricky when you are using indices of or values of (ie, a sparse bind array).
• PL/SQL does not assign a name to the -24381 error, but I encourage you to do that yourself, in your code, so you can handle it by name.

Here’s a version of the previous block that includes working with sql%bulk_exceptions.

declare  
   e_forall_failure exception;
   pragma exception_init (e_forall_failure, -24381);
   type namelist_t is table of varchar2 (5000);  
  
   enames_with_errors   namelist_t  
      := namelist_t ('ABC',  
                     'DEF',  
                     rpad ('BIGBIGGERBIGGEST', 1000, 'ABC'),  
                     'LITTLE',  
                     rpad ('BIGBIGGERBIGGEST', 3000, 'ABC'),  
                     'SMITHIE');  
begin  
   forall indx in 1 .. enames_with_errors.count save exceptions  
      update employees  
         set first_name = enames_with_errors (indx);  
exception
   when e_forall_failure
   then
      dbms_output.put_line (  
         'Updated ' || sql%rowcount || ' rows.');  
      dbms_output.put_line (sqlerrm); 

      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 "'
            || enames_with_errors (
                  sql%bulk_exceptions (indx).error_index)
            || '"');
         dbms_output.put_line (
               'Oracle error is '
            || sqlerrm (
                  -1 * sql%bulk_exceptions (indx).error_code));
      end loop;
end;

Now about that reference to “tricky” above….

Let’s change the block I just showed you to work with a sparse collection and indices of:

declare  
   e_forall_failure exception;
   pragma exception_init (e_forall_failure, -24381);
   type namelist_t is table of varchar2 (5000);  
  
   enames_with_errors   namelist_t  
      := namelist_t ('ABC',  
                     'DEF',  
                     rpad ('BIGBIGGERBIGGEST', 1000, 'ABC'));  
begin  
   enames_with_errors.delete (2);
   forall indx in indices of enames_with_errors save exceptions  
      update employees  
         set first_name = enames_with_errors (indx);  
exception
   when e_forall_failure
   then
      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 "'
            || enames_with_errors (
                  sql%bulk_exceptions (indx).error_index)
            || '"');
      end loop;
end;
/

ORA-01403: no data found

The reason this happens is that the error_index field in sql%bulk_exceptions tells us that the Nth attempt failed. But that doesn’t mean that the bind variable for that attempt will be found at the Nth index value. In this case, for example, the failure occurred when the second bind value was used. But it was in index value 3, not 2 (2 was deleted).

What this means is that when you use indices of or values of with save exceptions, you must use the error_index field value to find the related index value in the bind array. Or to put it another way (in code):

declare  
   e_forall_failure exception;
   pragma exception_init (e_forall_failure, -24381);
   type namelist_t is table of varchar2 (5000);  
  
   enames_with_errors   namelist_t  
      := namelist_t ('ABC',  
                     'DEF',  
                     rpad ('BIGBIGGERBIGGEST', 1000, 'ABC'));  

   function bind_array_index_for (
      bind_array_in    in namelist_t,
      error_index_in   in pls_integer,
      start_in         in pls_integer default null,
      end_in           in pls_integer default null)
      return pls_integer
   is
      l_index   pls_integer := nvl (start_in, bind_array_in.first);
   begin
      for indx in 1 .. error_index_in - 1
      loop
         l_index := bind_array_in.next (l_index);
      end loop;

      return l_index;
   end;                     
begin  
   enames_with_errors.delete (2);
   forall indx in indices of enames_with_errors save exceptions  
      update employees  
         set first_name = enames_with_errors (indx);  
exception
   when e_forall_failure
   then
      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 "'
            || enames_with_errors (
                  bind_array_index_for (
                  enames_with_errors,
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX))
            || '"');
      end loop;
end;

Well, I did say it was tricky, right?

When Bulk Means All

When your bulk operation means “Execute a statement for every bind variable, and keep on going if any of them fail,” it’s time to roll out the save exceptions clause.

When you do, ask yourself: is my collection always going to be dense? If so, easy-peasy. If not, you will need to use indices of or values of, in which case your exception handling gets tricky. But with the code I showed you above, you should be able to adapt quickly and easily.

And end up with the kind of robust code you want for your production applications.

P.S.: All the code shown in this blog post is also in this LiveSQL script.