DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

  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
    Apr 2001
    Location
    UK
    Posts
    137
    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;

  3. #3
    Join Date
    Oct 2000
    Posts
    90
    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.


  4. #4
    Join Date
    Aug 2000
    Posts
    21

    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

  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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




  6. #6
    Join Date
    Apr 2001
    Posts
    51

    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
  •  


Click Here to Expand Forum to Full Width