I am trying to delete about a million rows from a table using a pl/sql block but am getting an error ORA-01002: fetch out of sequence
Here is the sample of the block
2 cursor selectfordelete is select * from test where region not in ('REGROLL','WWF','WWR','CON','
3 'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON') for upd
5 for i in selectfordelete loop
6 delete from test where current of selectfordelete;
8 end loop;
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 5
I tried a simple test on my system and it seemed to work.
Committing while a cursor is open is a slightly dodgy area. I believe the ANSI standard is that you shouldn't do it and a commit will actually close the cursor (which would cause the fetch out of sequence error). Oracle normally allows it unless you're using PRO*C compiled in ANSI mode.
As a quick solution, you could try taking out the commit but you will need a large rollback segment to accommodate all 1 million rows.
In any case, committing after every single row is a bit inefficient. You could try committing less frequently. Use something like:
if mod(selectfordelete%rowcount,1000) = 0 then
I think you can not use commit in the cursor loop for FOR UPDATE OF
Here is the Oracle Error message
01002, 00000, "fetch out of sequence"
*Cause: 2) If the cursor has been opened with the FOR UPDATE clause,
fetching after a COMMIT has been issued will return the error.
Action: 2) Do not issue a COMMIT inside a fetch loop for a cursor
that has been opened FOR UPDATE.
I have executed your proceedure after commenting the commit inside the loop. It's working fine.
But the problem is you are deleting a million records.