I want to follow up on my post from yesterday with more details. A junior developer had to write a PL/SQL script that would update upwards of 10 million rows in the database. To make things run fast, she put FOR UPDATE in the cursor SQL. Then the actual update used WHERE CURRENT OF. This sounds like a solid design.The problem was that a batching commit mechanism was added in the middle of the loop through the cursor. The result was that after the first batch was committed, the next iteration through the loop caused an ORA-01002: fetch out of sequence. The thing to know is that the FOR UPDATE works up until the next commit. After that you are done with the FOR UPDATE cursor. But the script was coded so that it kept on trying to loop after the commit. Is there a solution for batch commits with a cursor? Yes. Don't use FOR UPDATE. Or break up your cursor into many cursors, each of which does a single COMMIT.
