Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    10

    Red face Unanswered: ORA-01002 for CURSOR FOR LOOP

    I received an ORA-01002 "fetch out of sequence" Error Message while running the stored procedure attached herewith. The Line Number of error was 190 (which is a FOR LOOP cursor call).

    Can any of you please go through it and help me figure out the cause of the error?

    Thanks in advance! :-)
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Why do you have two 'BEGIN's in your proc?

    Have you tried it with just the insert inside that cursor and not the second begin clause?

    Personally, I have never seen two begin statements in one proc. Am I crazy or what?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Location
    Germantown, MD
    Posts
    5

    reviewing ORA error

    Originally posted by The_Duck
    Why do you have two 'BEGIN's in your proc?

    Have you tried it with just the insert inside that cursor and not the second begin clause?

    Personally, I have never seen two begin statements in one proc. Am I crazy or what?
    I cannot comment on your mental state, but I can state that the use of an anonymous block around inserts is the preferred way of handling SQL exceptions. It allows the procedure to capture any errors specific to the SQL statement and record details (I use a log table, myself) but the procedure continues instead of exiting prematurely.

  4. #4
    Join Date
    Jul 2003
    Location
    Germantown, MD
    Posts
    5

    Re: ORA-01002 for CURSOR FOR LOOP

    Originally posted by gopalkamat
    I received an ORA-01002 "fetch out of sequence" Error Message while running the stored procedure attached herewith. The Line Number of error was 190 (which is a FOR LOOP cursor call).

    Can any of you please go through it and help me figure out the cause of the error?

    Thanks in advance! :-)
    I have one question...
    Could you use an EXIT WHEN statement instead of an IF statement for your debugging? I'm not certain how the PL/SQL parser treats the exit command. On the other hand, using EXIT WHEN counter > 100 is common.

    You have a significant number of cursors, some only populating linear variables (versus an array). I have seen this technique used to ensure you don't encounter a TOO_MANY_ROWS error, but you could also use anonymous blocks with a SELECT statement and use an order by to capture the latest record. I think this would be easier to maintain than a procedure full of cursors where you have to keep track of all the END LOOP statements.

    I realize this might not address your problem, but I noticed these things as I was looking through your code...

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Lightbulb Fetch out of sequence

    Hi
    I had a question about the procedure you are using where u get the infamous "fetch out of sequence".
    Few days back I had a similar experience while using CLOBs in my select statement, which i was using also to update.

    By the way, is your autoCommit set to true or false in whatever u r doing.
    Try doing something like autoCommit(false), and after your query is done, do autoCommit(true). This probably would work for you.

Posting Permissions

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