BULK COLLECT EXCEPTION HANDLING

As we all know how to use BULK COLLECT to handle performance issues while working with large data.
In this BLOG I help you understand how to handle exceptions while using BULK COLLECT.


DECLARE
lt_projects_tbl  CURSOR_PROJ%ROWTYPE;
BEGIN



OPEN  cursor_PROJ;

FETCH CURSOR_PROJ BULK COLLECT INTO lt_projects_tbl ;



IF lt_projects_tbl.COUNT<>0

THEN --IF OF PROJECT LOOP


   BEGIN



   FORALL i IN 1..lt_projects_tbl.COUNT SAVE EXCEPTIONS

  DELETE FROM custom_table_tb

WHERE  unique_id =lt_projects_tbl(i).unique_id;



EXCEPTION

  WHEN ge_ex_bulk_errors

  THEN



  gn_bulk_error_count  :=  SQL%BULK_EXCEPTIONS.COUNT;

  gc_error_message  :=  SUBSTR ( TO_CHAR ( gn_bulk_error_count ) || ' Errors while deleting custom table.' , 1, 2000 );


  FOR l_projects_stg_ex_bulk_indx IN 1 .. gn_bulk_error_count

  LOOP



-- Resetting for the next iteration of the loop

gn_bulk_error_code           :=  NULL;

gn_bulk_error_index          :=  NULL;

l_bulk_error_project_stg_id  :=  NULL;

gc_bulk_error_messsage       :=  NULL;



gn_bulk_error_code            :=  SQL%BULK_EXCEPTIONS ( l_projects_stg_ex_bulk_indx ).error_code;

gn_bulk_error_index           :=  SQL%BULK_EXCEPTIONS ( l_projects_stg_ex_bulk_indx ).error_index;

l_bulk_error_project_stg_id   :=   lt_projects_tbl( gn_bulk_error_index ).project_stg_id;

gc_bulk_error_messsage        :=  'Unable to delete data from projects staging table Error: '|| SQLERRM ( -gn_bulk_error_code );




UPDATE custom_table_tb  XPS

   SET XPS.status                  =  'ERROR'

, XPS.status_description      =  SUBSTR ( XPS.status_description || CHR(10)  || gc_bulk_error_messsage, 1, 4000 )

     , XPS.program_update_date     =  SYSDATE

, XPS.last_update_date        =  SYSDATE

WHERE unique_id                    =  l_bulk_error_unique_id;



  END LOOP; 



END ;