how dows bulk update flow of execution works

Question

I am putting up some code which will be copying billions of data from one table to another and we don't want the procedure to stop in case of exception. so i am putting up the script like (not putting 100% compilable syntax)

dml_errors      exception;
errors          number;
error_count     number;
pragma exception_init (dml_errors, -24381);

---------
open cursor;
begin loop;
   fetch cursor bulk collect into tbl_object limit batch_size;
   exit when tbl_object.count = 0;
   -- perform business logic
   begin
       forall in tbl_object save exceptions;
          insert into table;
          tbl_object.delete;
   exception
       when dml_errors then
                errors      :=  sql%bulk_exceptions.count;
                error_count :=  error_count + errors;
                insert into log_table (tstamp, line) values (sysdate, SUBSTR('[pr_procedure_name:'||r_guid||'] Batch # '||batch_number - 1||' had '||errors||' errors',1,300));
   end;
end loop;
close cursor;
end procedure;

now based on this pseduo-code I have 2 questions

1. I am deleting my collection in forall loop. If there is an exception and i decided to fetch some information from my collection in dml_errors block, would i have collection elements in there ? If yes, then would it safe to delete them after logging ?
2. Since i am keeping my forall in begin-exception-end block, would it keep iterating ?

Answer 1

Are you sure you need to use PL/SQL here? Unless you're doing a lot of processing in the business logic that you aren't showing us that can't be done in SQL, I would tend to use DML error logging. That will be more efficient, less code, and give you better logging.

DBMS_ERRLOG.CREATE_ERROR_LOG( 'DESTINATION_TABLE' );

INSERT INTO destingation_table( <<columns>> )
  SELECT <<columns>>
    FROM source_table
  LOG ERRORS 
  REJECT LIMIT UNLIMITED;

I don't see any reason to delete from your tbl_object collection. That doesn't seem to be gaining you anything. It's just costing some time. If your indentation is indicative of your expected control flow, you're thinking that the delete is part of the forall loop-- that would be incorrect. Only the insert is part of the forall, the delete is a separate operation.

If your second question is "If exceptions are raised in iteration N, will the loop still do the N+1th fetch", the answer is yes, it will.

One thing to note-- since error_count is not initialized, it will always be NULL. You'd need to initialize it to 0 in order for it to record the total number of errors.