ORA-01002: fetch out of sequence. Please Help

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

Remove the COMMIT; command from line 7.

That should help you.

Sam

Hi Sam,
The reason I have a commit there is becuase the cursor fetches mote than a million records and deleteing them without committing will run out of rollback space.

The only reason I am using a cursor here is to do commits otherwise I could have used a simple delete command.

Is there any other way i can delete a million rows.

Thanks
Anurag

This one I think you can use the

SET AUTOCOMMIT #

Get it autocommitted after certain number of operations. That way I hope that you could solve this problem on doing the execution in SQL.


Sam

Sorry, but it seems to me you should OPEN cursor first
and don't forget to CLOSE it at the end.

The cursor FOR loop would implicitly open the cursor and close the cursor.

Sam

Anurag,

The way you are doing this is totaly wrong. I can't say why you are getting this "Fetch out of sequence" error, but even if you didn't get this one you will most certanly get another nasty one: ORA-1555: Snapshot to old...

What you are doing is called "fetch accros commit" - you open a cursor, feTch some record from it, perform some changes to the table you are fetching from, commit the changes and continue fetching from this same cursor without closing it and opening it again. The perfect scenario for ORA-1555. The more often you commit, the sooner you'll get ORA-1555.

The most simple way for your example would be to commit every N records (say N=100,000), but to open a new cursor (implicit or explicit one) after each commit:

LOOP
DELETE FROM test WHERE rownum <= 100000 AND ....;
EXIT WHEN SQL%ROWCOUNT = 0;
END;

Hi Jurij,

I tried your solution and it also does not work.
Below is the sample of the code. It is not able to delete even 50 records at a time and gives a ORA-01562: failed to extend rollback segment number 3. I tried increasing the roll back segment size but I think that wont help as it is not commiting and trying to delete all the 1 million records.

Please point out what am i doing wrong here.

Thanks
http://www.plauder-smilies.de/sad/shakehead.gifAnurag


SQL> ed
Wrote file afiedt.buf

1 declare
2 cnt number := 0;
3 begin
4 select count(*) into cnt from test;
5 cnt := cnt/10000;
6 cnt := ceil(cnt);
7 for i in 1..cnt loop
8 loop
9 delete from test where rownum <=50 and region not in ('REGROLL','WWF','WWR','CON','REGWEST','
10 'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON');
11 EXIT WHEN SQL%ROWCOUNT = 0;
12 end loop;
13 commit;
14 end loop;
15* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 3
ORA-01650: unable to extend rollback segment RBS2 by 64 in tablespace RBS
ORA-06512: at line 9

[Edited by anuragmin on 06-08-2001 at 03:20 PM]

how about puting commit after EXIT WHEN SQL%ROWCOUNT = 0; inside the inner loop?

Nope,
I tried putting the commit inside the loop after the exit when command but still it is giving me the error

declare
cnt number := 0;
begin
select count(*) into cnt from test;
cnt := cnt/10000;
cnt := ceil(cnt);
for i in 1..cnt loop
loop
delete from test where rownum <=50 and region not in ('REGROLL','WWF','WWR','CON','REGWEST','
'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON');
EXIT WHEN SQL%ROWCOUNT = 0;
commit;
end loop;
end loop;
end;
/

declare
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 3
ORA-01562: failed to extend rollback segment number 3
ORA-06512: at line 9

http://www.dbasupport.com/forums/ Anurag

Anurag,

I'm sorry, I forgot to include a COMMIT into my sample code. It should really be

BEGIN
LOOP
DELETE FROM test WHERE rownum <= 50 AND ....;
COMMIT;
EXIT WHEN SQL%ROWCOUNT = 0;
END LOOP;
END;
/

No need for another outer loop as in your last post, as all the deletes are taken care of by the inner loop. Your outer loop would only cause ceil(cnt/10000) number of full table scans after all the rows have allready been deleted.

Now if you really have problems with deleted even 50 rows at a time then something is *very wrong* with your rollback segments settings. BTW, what is the avarage row size in the table TEST? What I think is happening is some of your rollback segments have extended to a very large size occupying all of the available space in your RB tablespace, while others are very (extremely?) small, like your RB #3, and are unable to extend. Probably you should shrink the big rollback segments to a normal size and also set the OPTIMAL parameter for all your RB segments. Or maybe create a special large rollback segment and assigne it to your transaction before each delete in your loop.


[Edited by jmodic on 06-08-2001 at 04:08 PM]