jcon.no: Oracle blog

This one I think I know the answer allready, but just wanted to test for sure.
The question I asked myself is “When does FORALL SAVE EXCEPTIONS jump to the EXCEPTION clause?”. I’m pretty sure the answer is “After it completes the FORALL, and if – and only if – there are exceptions”.

To prepare for the test case run the following:

-- drop table big_table purge;
create table big_table as 
   select rownum ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, 
          DATA_OBJECT_ID,
          decode( mod(rownum,100000), 1, rpad('*',20,'*'), OBJECT_TYPE ) object_type,
          CREATED, LAST_DDL_TIME, sysTIMESTAMP ts, STATUS, TEMPORARY, 
          GENERATED, SECONDARY
   from all_objects where rownum<=10;

alter table big_table add constraint big_table_pk primary key (id);

select count(*) from big_table;

Then run the test case below three times (trying to insert two rows in the BIG_TABLE):

1. Run as is (Both inserts should fail)
2. Uncomment the “l_data(1).id:=11;” and run again (One insert should fail, and one should succed)
3. Comment the line from 2), and uncomment the next two lines (“l_data(1).id:=12; l_data(2).id:=13;”) => (Both inserts should succeed)

My test case:

set serveroutput on size 100000
DECLARE
   cursor C is
      select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
           decode( mod(rownum,100000), 1, rpad('*',20,'*'), OBJECT_TYPE ) object_type,
           CREATED, LAST_DDL_TIME, sysTIMESTAMP ts, STATUS, TEMPORARY, GENERATED, SECONDARY
      from big_table where rownum<=2;
      
   type array is table of c%rowtype;
   l_data      array;
   
   dml_errors  EXCEPTION;
   PRAGMA exception_init(dml_errors, -24381);
   l_errors    number := 0;
   l_errno     number;
   l_msg       varchar2(4000);
   l_msg2      varchar2(1000) := 'Dummy';
   l_idx       number;
   l_cnt       number;
   
BEGIN
   open c;
   loop
      fetch c bulk collect into l_data limit 100;
      --l_data(1).id:=11; -- Use the second time
      --l_data(1).id:=12; -- Use the third time
      --l_data(2).id:=13; -- Use the third time
      begin
         forall i in 1 .. l_data.count SAVE EXCEPTIONS
            insert into big_table values l_data(i);
         
         l_cnt := sql%rowcount;
         dbms_output.put_line('Between FORALL and EXCEPTION: '||l_cnt);
         
      exception
         when DML_ERRORS then
            l_cnt    := sql%rowcount;
            l_errors := sql%bulk_exceptions.count;
            for i in 1 .. l_errors
            loop
               l_errno := sql%bulk_exceptions(i).error_code;
               l_msg   := sqlerrm(-l_errno);
               l_idx   := sql%bulk_exceptions(i).error_index;
               
               dbms_output.put_line('ERROR - ID: ' || l_data(l_idx).ID || ', object: '||l_data(l_idx).OBJECT_NAME);
               l_msg2  := 'Error(s) happend (count: ' || l_errors || ')';
            end loop;
        end;
        exit when c%notfound;
   end loop;
   close c;
   
   if (l_errors = 0) then
      l_cnt := sql%rowcount;
      l_msg2:= 'ALL insert successfully completed';
      dbms_output.put_line('AFTER (FORALL count): ' || l_cnt);
      dbms_output.put_line('Message     : '||l_msg2);
   else
      dbms_output.put_line('EXCEPTION (FORALL count): ' || l_cnt);
      dbms_output.put_line('Message  : '||l_msg2);
   end if;

end;
/

Output from first run (both inserts failes):

ERROR - ID: 1, object: ORA$BASE
ERROR - ID: 2, object: DUAL
EXCEPTION (FORALL count): 0
Message  : Error(s) happend (count: 2)

PL/SQL procedure successfully completed.

Out put from second run (one insert fails and one succeeds):

ERROR - ID: 2, object: DUAL
EXCEPTION (FORALL count): 1
Message  : Error(s) happend (count: 1)

PL/SQL procedure successfully completed.

Output from third run (both inserts succeed):

Between FORALL and EXCEPTION: 2
AFTER (FORALL count): 2
Message     : ALL insert successfully completed

PL/SQL procedure successfully completed.

So the FORALL behaves just like expected:
– If exceptions in the FORALL, the PLSQL jumps to the EXCEPTION section straight after the FORALL completes (with ORA-24381)
– If NOT exceptions in the FORALL, the code continue to run after the FORALL