title: bulk collect using "for update"
body: <p>I run into an interesting and unexpected issue when processing records in Oracle (11g) using BULK COLLECT. </p>
  
  <p>The following code was running great, processing through all million plus records with out an issue:</p>
  
  <pre><code>-- Define cursor
  cursor My_Data_Cur Is
  Select col1
        ,col2
  from My_Table_1;
  …
  
  -- Open the cursor
  open My_Data_Cur;
  
  -- Loop through all the records in the cursor
  loop
  
    -- Read the first group of records
    fetch My_Data_Cur
    bulk collect into My_Data_Rec
    limit 100;
  
    -- Exit when there are no more records to process
    Exit when My_Data_Rec.count = 0;
  
    -- Loop through the records in the group
    for idx in 1 .. My_Data_Rec.count
    loop
      … do work here to populate a records to be inserted into My_Table_2 …
    end loop;
  
    -- Insert the records into the second table
    forall idx in 1 .. My_Data_Rec.count
    insert into My_Table_2…;
  
    -- Delete the records just processed from the source table
    forall idx in 1 .. My_Data_Rec.count
    delete from My_Table_1 …;
  
    commit;
  end loop;
  </code></pre>
  
  <p>Since at the end of processing each group of 100 records (limit 100) we are deleting the records just read and processed, I though it would be a good idea to add the “for update” syntax to the cursor definition so that another process couldn’t update any of the records between the time the data was read and the time the record is deleted.</p>
  
  <p>So, the only thing in the code I changed was…</p>
  
  <pre><code>cursor My_Data_Cur
  is
    select col1
          ,col2
  from My_Table_1
  for update;
  </code></pre>
  
  <p>When I ran the PL/SQL package after this change, the job only processes 100 records and then terminates.  I confirmed this change was causing the issue by removing the “for update” from the cursor and once again the package processed all of the records from the source table.</p>
  
  <p>Any ideas why adding the “for update” clause would cause this change in behavior?  Any suggestions on how to get around this issue?  I’m going to try starting an exclusive transaction on the table at the beginning of the process, but this isn’t an idea solution because I really don’t want to lock the entire table which processing the data.</p>
  
  <p>Thanks in advance for your help,</p>
  
  <p>Grant</p>