Oracle Live SQL - Script: Step by step for Feuertip #15 on save exceptions

Statement 1

create table employees as select * from hr.employees

Table created.

Statement 2

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);
   rollback;         
exception
   when others
   then
      dbms_output.put_line ('Updated ' || sql%rowcount || ' rows.');
      dbms_output.put_line (dbms_utility.format_error_stack);
      rollback;
end;

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

Statement 3

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);
   rollback;         
exception
   when others
   then
      dbms_output.put_line ('Updated ' || sql%rowcount || ' rows.');
      dbms_output.put_line (dbms_utility.format_error_stack);
      rollback;
end;

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

Statement 4

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);  
   rollback;
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;
 
      rollback;  
end;

Statement processed.
Updated 428 rows.
ORA-24381: error(s) in array DML
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

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);   
   rollback;
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; 
      rollback;
end;

ORA-01403: no data found ORA-06512: at line 21
ORA-06512: at line 12
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00100

Statement 6

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);  
   rollback; 
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; 
      rollback;
end;

Statement processed.
Error 1 occurred on index 2 attempting to update name to "BIGBIGGERBIGGESTABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC"

Step by step for Feuertip #15 on save exceptions

Additional Information