Oracle Live SQL - Script: SAVE EXCEPTIONS and FORALL

Statement 1

CREATE TABLE employees 
AS 
   SELECT * FROM hr.employees

Table created.

Statement 2

PL/SQL raises ORA-24381 if at least one statement failed in a FORALL that uses SAVE EXCEPTION. It is best to handle exceptions by name, but this error is not named in STANDARD. So we do it ourselves!

Name that -24381! (once)

CREATE OR REPLACE PACKAGE std_errs  
IS  
   failure_in_forall   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (failure_in_forall, -24381);  
END;

Package created.

Statement 3

You can't update a first_name to a string of 1000 or 3000 bytes. But without SAVE EXCEPTIONS we never get past the third element in the bind array. The employees table has 107 rows. How many were updated?

First, Without SAVE EXCEPTIONS

DECLARE  
   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  
      UPDATE employees  
         SET first_name = enames_with_errors (indx);  
  
   ROLLBACK;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line (  
         'Updated ' || SQL%ROWCOUNT || ' rows.');  
      DBMS_OUTPUT.put_line (SQLERRM);  
      ROLLBACK;  
END;

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

Statement 4

Execute every generated statement no matter how of them fail, please! Now how many rows were updated? Notice that with SAVE EXCEPTIONS in place, I can take advantage of SQL%BULK_EXCEPTIONS to see how statements failed, and which ones, and with which error. Can you see, however, the difference between the error information displayed in the previous step and this one?

Now With SAVE EXCEPTIONS

DECLARE  
   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);  
  
   ROLLBACK;  
EXCEPTION  
   WHEN std_errs.failure_in_forall  
   THEN  
      DBMS_OUTPUT.put_line (SQLERRM);  
      DBMS_OUTPUT.put_line (  
         'Updated ' || SQL%ROWCOUNT || ' rows.');  
  
      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;  
  
      ROLLBACK;  
END;

Statement processed.
ORA-24381: error(s) in array DML
Updated 428 rows.
Error 1 occurred on index 3 attempting to update name to "BIGBIGGERBIGGESTABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC"
Oracle error is ORA-12899: value too large for column  (actual: , maximum: )
Error 2 occurred on index 5 attempting to update name to "BIGBIGGERBIGGESTABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCAB"
Oracle error is ORA-12899: value too large for column  (actual: , maximum: )

Statement 5

If the array that drives the FORALL statement (the bind array) is not dense and you use INDICES OF, you can run into some complications.

Now Explore SAVE EXCEPTIONS with Sparse Bind Arrays

CREATE TABLE plch_employees  
(  
   employee_id   INTEGER,  
   last_name     VARCHAR2 (100),  
   salary        NUMBER (8, 0)  
)

Table created.

Statement 6

BEGIN 
   INSERT INTO plch_employees 
        VALUES (100, 'Ninhursag ', 1000000); 
 
   INSERT INTO plch_employees 
        VALUES (200, 'Inanna', 1000000); 
 
   INSERT INTO plch_employees 
        VALUES (300, 'Enlil', 1000000); 
 
   COMMIT; 
END;

Statement processed.

Statement 7

INDICES OF is a great feature: you can use it when your bind array is not densely filled. And you can use BETWEEN to further finesse which elements in the bind array are used to generate statements. But then it is a challenge to correlate the SQL%BULK_EXCEPTIONS ERROR_INDEX value back to the right index value in the bind array! Which index values do you think will be displayed here?

INDICES OF - BETWEEN - Complications!

DECLARE  
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE  
      INDEX BY PLS_INTEGER;  
  
   l_employees         employee_aat;  
BEGIN  
   l_employees (1) := 100;  
   l_employees (2) := 200;  
   l_employees (3) := 300;  
   l_employees (4) := 200;  
   l_employees (5) := 100;  
  
   FORALL l_index IN INDICES OF l_employees BETWEEN 3 AND 5  
     SAVE EXCEPTIONS  
      UPDATE plch_employees  
         SET salary =  
                  salary  
                * CASE employee_id WHEN 200 THEN 1 ELSE 100 END  
       WHERE employee_id = l_employees (l_index);  
EXCEPTION  
   WHEN std_errs.failure_in_forall  
   THEN  
      DBMS_OUTPUT.put_line ('Errors:');  
  
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT  
      LOOP  
         DBMS_OUTPUT.put_line (  
            SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);  
      END LOOP;  
  
      ROLLBACK;  
END;

Statement processed.
Errors:
1
3

Statement 8

Now I offer a helpful utility, bind_array_index_for, that figures out the actual index value in the bind array from the ERROR_INDEX value and the start/end values in the INDICES OF BETWEEN's clause.

Correlate ERROR INDEX Back to Bind Array

DECLARE 
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE 
      INDEX BY PLS_INTEGER; 
 
   l_employees   employee_aat; 
 
   FUNCTION bind_array_index_for (bind_array_in    IN employee_aat, 
                                  error_index_in   IN PLS_INTEGER, 
                                  start_in         IN PLS_INTEGER) 
      RETURN PLS_INTEGER 
   IS 
      l_index   PLS_INTEGER := start_in; 
   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 
   BEGIN 
      l_employees (1) := 100; 
      l_employees (100) := 200; 
      l_employees (500) := 300; 
 
      FORALL l_index IN INDICES OF l_employees SAVE EXCEPTIONS 
         UPDATE plch_employees 
            SET salary = salary * CASE employee_id WHEN 200 THEN 1 ELSE 100 END 
          WHERE employee_id = l_employees (l_index); 
   EXCEPTION 
      WHEN std_errs.failure_in_forall 
      THEN 
         DBMS_OUTPUT.put_line ('Errors:'); 
 
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT 
         LOOP 
            DBMS_OUTPUT.put_line ( 
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX 
               || ' -> ' 
               || bind_array_index_for (l_employees, 
                                        SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX, 
                                        l_employees.FIRST)); 
         END LOOP; 
 
         ROLLBACK; 
   END; 
 
   BEGIN 
      l_employees (1) := 100; 
      l_employees (2) := 200; 
      l_employees (3) := 300; 
      l_employees (4) := 200; 
      l_employees (5) := 100; 
 
      FORALL l_index IN INDICES OF l_employees BETWEEN 3 AND 5 SAVE EXCEPTIONS 
         UPDATE plch_employees 
            SET salary = salary * CASE employee_id WHEN 200 THEN 1 ELSE 100 END 
          WHERE employee_id = l_employees (l_index); 
   EXCEPTION 
      WHEN std_errs.failure_in_forall 
      THEN 
         DBMS_OUTPUT.put_line ('Errors:'); 
 
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT 
         LOOP 
            DBMS_OUTPUT.put_line ( 
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX 
               || ' -> ' 
               || bind_array_index_for (l_employees, 
                                        SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX, 
                                        3)); 
         END LOOP; 
 
         ROLLBACK; 
   END; 
END;

Statement processed.
Errors:
1 -> 1
3 -> 500
Errors:
1 -> 3
3 -> 5

SAVE EXCEPTIONS and FORALL - An Exploration

Additional Information