Bulk collect with bulk binding Example

declare

  type emp_tab is RECORD(
                         segment1 gl_code_combinations.segment1%type,
                         segment2 gl_code_combinations.segment2%type,
                         segment3 gl_code_combinations.segment3%type,
                         segment4 gl_code_combinations.segment4%type,
                         segment5 gl_code_combinations.segment5%type,
                         error_flag varchar2(5),
                         error_message varchar2(1000)
                       );

          
  TYPE dept_emp_tab IS TABLE OF emp_tab INDEX BY BINARY_INTEGER;
 

  dept_emp_tab1 dept_emp_tab;
  dept_emp_tab2 dept_emp_tab;


  cursor c is select
                  segment1,
                  segment2,
                  segment3,
                  segment4,
                  segment5,
                  null,
                  null
            from GL_CODE_COMBINATIONS;
  
   exce exception;
   PRAGMA EXCEPTION_INIT (exce, -24381);
   v_flag varchar2(5);
   v_message varchar2(1000);
   errors number;
  
 
   begin
    open c;
     loop
       fetch c bulk collect into dept_emp_tab1;
       exit when c%notfound;
      end loop;
      
   
       for i in 1..dept_emp_tab1.count loop
        v_flag:='N';
        v_message:=null;
     
          if  dept_emp_tab1(i).segment1 is null or dept_emp_tab1(i).segment1 = '-' then
           v_flag:='E';
           v_message:='segment1  have null vlaue or no data ';
      
          end if;
   
         if  dept_emp_tab1(i).segment2 is null or dept_emp_tab1(i).segment2 = '-' then
         v_flag:='E';
         v_message:=v_message||'segment2 have null vlaue or no data ';
        
         end if;
     
         if  dept_emp_tab1(i).segment3 is null or dept_emp_tab1(i).segment3 = '-'then
          v_flag:='E';
          v_message:=v_message||'segment3 have null vlaue or no data ';
        
         end if;
 

         if  dept_emp_tab1(i).segment4 is null or dept_emp_tab1(i).segment4 = '-'then
         v_flag:='E';
         v_message:=v_message||'segment4 have null vlaue or no data ';
       
         end if;
         
         if dept_emp_tab1(i).segment5 is null or dept_emp_tab1(i).segment5 = '-' then
          v_flag:='E';
          v_message:=v_message||'segment5 have null vlaue or no data ';
       
         end if;
     
        if v_flag = 'E' then
        dept_emp_tab1(i).error_flag:=v_flag;
        dept_emp_tab1(i).error_message:=v_message;
        
        else
        dept_emp_tab2(i).segment1:=dept_emp_tab1(i).segment1;
        dept_emp_tab2(i).segment2:=dept_emp_tab1(i).segment2;
        dept_emp_tab2(i).segment3:=dept_emp_tab1(i).segment3;
        dept_emp_tab2(i).segment4:=dept_emp_tab1(i).segment4;
        dept_emp_tab2(i).segment5:=dept_emp_tab1(i).segment5;
        end if;
       
       end loop;
    dbms_output.put_line(dept_emp_tab1.count);
     dbms_output.put_line(dept_emp_tab2.count);
    
        .
       
    forall j in dept_emp_tab2.first..dept_emp_tab2.last save exceptions
     insert into valid_tab2 values(dept_emp_tab2(j).segment1,
                                   dept_emp_tab2(j).segment2,
                                   dept_emp_tab2(j).segment3,
                                   dept_emp_tab2(j).segment4,
                                   dept_emp_tab2(j).segment5
                                  );
     Exception
         when exce then
         errors := SQL%BULK_EXCEPTIONS.COUNT;
          FOR k IN 1 .. errors LOOP  
            
          
            dbms_output.put_line('Error message is ' ||k|| SQLERRM(-SQL%BULK_EXCEPTIONS(k).ERROR_CODE));
           end loop;
     end;