Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table employee( employee_id  integer  primary key
                     , employee_name varchar2(20) 
                     ); 
insert into employee(employee_id, employee_name)
  select 1,'George' from dual union all 
  select 2,'Henreatta' from dual union all
  select 3,'Susan' from dual union all
  select 4,'Sam' from dual union all
  select 5,'Ann' from dual;   
5 rows affected

DECLARE
--
bulk_errors       exception;
PRAGMA exception_init(bulk_errors, -24381);
--
--
CURSOR cursEmployee IS
SELECT EMPLOYEE_ID, EMPLOYEE_NAME
FROM EMPLOYEE;

TYPE employee_table IS TABLE OF cursEmployee%rowtype;
employee_rec            employee_table;
--
BEGIN
--  
        OPEN cursEmployee;
        FETCH cursEmployee BULK COLLECT INTO employee_rec LIMIT 3;
        --
        WHILE employee_rec.COUNT != 0 LOOP
            --
            FORALL indx IN INDICES OF employee_rec save exceptions
                --
                
                INSERT INTO EMPLOYEE (
                    EMPLOYEE_ID,
                    EMPLOYEE_NAME
                    )
                VALUES (
                    employee_rec(indx).EMPLOYEE_ID,
                    employee_rec(indx).EMPLOYEE_NAME
                    );
            --
            COMMIT;
            --
            FETCH cursEmployee BULK COLLECT INTO employee_rec LIMIT 10000;
            --
        
        END LOOP;
   exception when bulk_errors then
 
        for i in 1 .. sql%bulk_exceptions.COUNT loop
            declare e_indx integer :=  sql%bulk_exceptions(i).error_index;
            begin
              dbms_output.put_line('Employee Id : ' || employee_rec(e_indx).EMPLOYEE_ID);
              dbms_output.put_line('Employee Name : ' || employee_rec(e_indx).EMPLOYEE_NAME);
              dbms_output.put_line('Error Message: '||sqlerrm(-sql%bulk_exceptions(i).error_code));
            end ; 
        end loop;
           
    CLOSE  cursEmployee;
       
END;
/
1 rows affected

dbms_output:
Employee Id : 1
Employee Name : George
Error Message: ORA-00001: unique constraint (.) violated
Employee Id : 2
Employee Name : Henreatta
Error Message: ORA-00001: unique constraint (.) violated
Employee Id : 3
Employee Name : Susan
Error Message: ORA-00001: unique constraint (.) violated

declare

  bulk_errors       exception;
  pragma exception_init(bulk_errors, -24381);

  k_bulk_buffer_size constant integer :=3;
  
  cursor cursemployee is
     select employee_id, employee_name
       from employee;

     type employee_table is table of cursemployee%rowtype;
     employee_rec            employee_table;

begin
        open cursemployee;

        loop -- empolyee table loop        
           fetch cursemployee 
            bulk collect 
            into employee_rec 
           limit k_bulk_buffer_size;
           
           begin -- while employees buffer block (will contain the exception block)
              forall indx in indices of employee_rec save exceptions  
                   insert into employee ( employee_id,employee_name)
                   values ( employee_rec(indx).employee_id, employee_rec(indx).employee_name);
              commit;
            
            /* moved exception block */
           exception 
           when bulk_errors then
                declare --  exception  block
                   e_indx integer;                         
                begin
                   for i in 1 .. sql%bulk_exceptions.count                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                   loop -- bulk errors loop 
                       e_indx :=  sql%bulk_exceptions(i).error_index;
                       dbms_output.put_line('-----');
                       dbms_output.put ('Employee Id: ' || employee_rec(e_indx).employee_id);
                       dbms_output.put_line(' Name: ' || employee_rec(e_indx).employee_name);
                       dbms_output.put_line('Error Message: '||sqlerrm(-sql%bulk_exceptions(i).error_code));                     
                   end loop; --bulk errors loop
                end; --  exception  block;            
           end; -- while employees buffer block   
          exit when employee_rec.count < k_bulk_buffer_size;
      end loop; -- empolyee table loop 

    close  cursemployee;
       
end; -- procedure
/
1 rows affected

dbms_output:
-----
Employee Id: 1 Name: George
Error Message: ORA-00001: unique constraint (.) violated
-----
Employee Id: 2 Name: Henreatta
Error Message: ORA-00001: unique constraint (.) violated
-----
Employee Id: 3 Name: Susan
Error Message: ORA-00001: unique constraint (.) violated
-----
Employee Id: 4 Name: Sam
Error Message: ORA-00001: unique constraint (.) violated
-----
Employee Id: 5 Name: Ann
Error Message: ORA-00001: unique constraint (.) violated