title: Oracle Bulk Collect with Limit and For All Not Processing All Records Correctly
body: <p>I need to process close to 60k records of an Oracle table through a stored procedure. The processing is that for each such row, I need to delete and update a row in a second table and insert a row in a third table.</p>
  
  <p>Using cursor looping, the procedure takes around 6-8 hours to complete. If I switch to Bulk Collect with Limit, the execution time is reduced but processing is not correct. Following is the bulk collect version of the procedure</p>
  
  <pre><code>create or replace procedure myproc()
  is
      cursor c1 is select col1,col2,col3 from tab1 where col4=3;
      type t1 is table of c1%rowtype;
      v_t1 t1;
  begin
      open c1;
      loop
          fetch c1 bulk collect into v_t1 limit 1000;
          exit when v_t1.count=0;
          forall i in 1..v_t1.count
              delete from tab2 where tab2.col1=v_t1(i).col1;
          commit;
          forall i in 1..v_t1.count
              update tab2 set tab2.col1=v_t1(i).col1 where tab2.col2=v_t1(i).col2;
          commit;
          forall i in 1..v_t1.count
              insert into tab3 values(v_t1(i).col1,v_t1(i).col2,v_t1(i).col3);
          commit;
      end loop;
      close c2;
  end;
  </code></pre>
  
  <p>For around 20k of these records, the first delete operation is processed correctly but subsequent update and insert is not processed. For the remaining 40k records all three operations are processed correctly.</p>
  
  <p>Am I missing something? Also what is the maximum LIMIT value I can use with Bulk Collect?</p>