DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ORA-01002: fetch out of sequence. Please Help

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Angry

    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

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Remove the COMMIT; command from line 7.

    That should help you.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Sep 2000
    Posts
    362
    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

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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


    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Exclamation

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

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The cursor FOR loop would implicitly open the cursor and close the cursor.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2000
    Posts
    362

    Angry It still Does not work

    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
    Anurag


    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]

  9. #9
    Join Date
    Jun 2001
    Posts
    15
    how about puting commit after EXIT WHEN SQL%ROWCOUNT = 0; inside the inner loop?

  10. #10
    Join Date
    Sep 2000
    Posts
    362
    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

    Anurag

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width