Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    13

    Unanswered: fetch out of sequence error

    Hi,


    I am getting 'ORA-01002 fetch out of sequence error', when trying to excecute a procedure.

    The Procedure looks lik this:

    Procedure A
    ....
    cursor c1 is
    select case,hier_id
    from table_tb;

    Begin

    For rec in c1
    loop

    ......
    call to new procedure B
    where B does either a commit or rollback on every call to procedure B
    ......

    end loop;
    ....
    ...
    End;


    The procedures has no compilation error, but when excuting throws this --
    'ORA-01002 fetch out of sequence' error.

    I tried to search info about this error, but al I got was that such an error occurs when cursor has a for update with commit or rollback in between fetch.

    My cursor is specifically a select cursor with no fpr update but it makes call to a procedure which in turn performs commit or rollback.

    Im unable to solve this error, plz help

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Does "procedure B" have to commit (or rollback), unconditionally? Why wouldn't you COMMIT once, after the END LOOP statement in "procedure A"?

  3. #3
    Join Date
    May 2008
    Posts
    13
    Yes, Procedure B need Commit evrytime it is invoked

  4. #4
    Join Date
    May 2008
    Posts
    13
    Other than making procedure B an autonomous transaction, is ther any other way out?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by GRReddy
    Other than making procedure B an autonomous transaction, is ther any other way out?
    That depends. You have explained why procedure B must commit (or rollback) each time is is called. Maybe if it just rolled back to a savepoint you wouldn't need the commit?

Posting Permissions

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