-
Hi,
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
1 declare
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
4 begin
5 for i in selectfordelete loop
6 delete from test where current of selectfordelete;
7 commit;
8 end loop;
9* end;
10 /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 5
Please suuggest something.
Thanks
Anurag
-
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
commit;
end if;
-
Using 'where current of ...' in the delete could be causing the problem, especially after doing a commit. I would change it to be the primary key fields.
-
Hi
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.
BSR
-
1.
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
replace 'select *'
with 'select ROWID ROW_ID'
2.
delete from test where current of selectfordelete
replace 'where current of ..'
with 'where ROWID = selectfordelete.ROW_ID'
3.
commit every 100 or 1000 deletes
Hope this helps
Gert
-
The error u r facing here is due to issuing commit within the for loop .
In order to overcome this error what u just need to do is issue commit after the end loop; thats it. And the program will work fine. It has been tested at my end.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|