Bulk Collect - Save Exceptions

The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and still continue processing.

BULK COLLECT construct is used to work with batches of data rather than single record at a time. Whenever we have to deal with large amount of data, bulk collect provides considerable performance improvement.

Declare 
cursor cur_emp 
is 
select * from Emp; 
  
type array is table of c%rowtype;
l_data array; 
dml_errors EXCEPTION; 
PRAGMA exception_init(dml_errors, -24381); 
l_errors number; 
l_errno number; 
l_msg varchar2(4000); 
l_idx number;

Begin 
open cur_emp;
loop 
 fetch cur_emp bulk collect into l_data limit 100;
begin forall i in 1 .. l_data.count SAVE EXCEPTIONS 
 insert into t2 values l_data(i);
exit when cur_emp%notfound;
end loop;
close cur_emp;

Exception
when DML_ERRORS 
then
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 #’ || i || ‘ occurred during ‘||‘iteration #’ || l_idx);

DBMS_OUTPUT.PUT_LINE(‘Error message is ‘ || l_msg);

end loop;
end;
/

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical