Solved

Cursor ORA-01002: fetch out of sequence

Posted on 2002-04-24
10
70,664 Views
Last Modified: 2011-08-18
I have setup a cursor but keep getting this error message:

 exception: ORA-01002: fetch out of sequence

My code is as follows:

procedure validation_field_values IS

cursor regis_cur is
    select *
     from
          regis_import_zones
        where
             exported_flag = 'N'
      FOR UPDATE OF exported_flag ;
...

BEGIN

OPEN regis_cur;
  FETCH regis_cur INTO regis_rec;

  WHILE regis_cur%FOUND -- AND total_rec_count < 10
  LOOP
    total_record_count := total_record_count + 1;  
       
     SELECT COUNT(*)
        INTO regis_zone_count
        FROM siebel.s_lst_of_val
        WHERE VAL = regis_rec.zone AND TYPE = 'ZONE' ;
       
         IF regis_zone_count != 1 THEN
                                                         UPDATE regis_import_zones SET exported_flag = 'E' WHERE CURRENT OF regis_cur ;
        END IF ;

  FETCH regis_cur INTO regis_rec;
     
     
     
  END LOOP;
 
  CLOSE regis_cur;
 
  COMMIT;

...

END ;

Someone please help !!!! What am I doing wrong...

Regards,

Pardeep
0
Comment
Question by:pardeep_nagra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 11

Expert Comment

by:pennnn
ID: 6967204
I'm not sure exactly... Did you try to trace it and see when it fails - on the first fetch or later?
Anyway, here's another way to construct your loop, which is, I think, clearer and I haven't had problems with it:

OPEN regis_cur;
LOOP
  FETCH regis_cur INTO regis_rec;
  EXIT WHEN regis_cur%NOTFOUND;
  -- do your processing here

END LOOP;
CLOSE regis_cur;

Hope that helps!
0
 

Author Comment

by:pardeep_nagra
ID: 6967213
Sorry I missed a crucial step out.

There is a procedure call within the IF statement. Having traced it through this procedure perfoms a COMMIT within it.

Could that explain why I am getting the error message?

How could I re do the cursor so that it does not come up with this error message, as the procedure call updates a log table and is necassary.

Thanks,

Pardeep


 

0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6967255
Pardeep,
   Yes, the commit closes the transaction.  You could try issuing a savepoint (not sure that works), or loading the records into a varray (and then looping through them), or getting an ordered set and restarting at the point where you did a commit (using row_number()), or store (or just process without committing) the updates and commit at the end (this is usually the thing to do).  There are other approaches, but most involve a lot of complexity.

Good luck!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:pennnn
ID: 6967428
The easiest way would be to modify the procedure which is called within the IF statement. If you create it using PRAGMA AUTONOMOUS_TRANSACTION it will not commit the transaction within your main procedure, thus the cursor fetch will not fail (I hope!).
You need to add just one line in your procedure (the one which is called in the IF statement):
CREATE OR REPLACE PROCEDURE proc IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- everything in the procedure remains unchanged...
END;

The procedure will start an independent transaction, commit and then it will return to the main transaction...
I hope that will solve your problem!
0
 
LVL 5

Accepted Solution

by:
sora earned 200 total points
ID: 6967763
Just to add to Dr.SQL's comments, using a COMMIT or ROLLBACK with a FOR UPDATE OF cursor, invalidates the cursor. If your cursor is not using a FOR UPDATE OF then it will work fine.

So, in your case, either move the COMMIT to after the cursor is closed and leave the rest as it is

OR

remove the FOR UPDATE OF clause from the cursor declaration and when you update inside the cursor, don't use the CURRENT OF clause - use the exact clause (whatever that may be in your case)


sora
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6967900
i got this error, if the rollback-segment is too small
0
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6968119
Your for update implies that you want some sort of locking-mechanism withing your procedure. If you want to keep this, and if possible try rewriting the committing procedure:
add an extra parameter p_commit boolean default TRUE
to this procedure and check within the logic on this parameter if it should commit or not.
In this way all other logic in your application will continue to work with the same behavior and --with calling
the proc with parameter p_commit=> FALSE -- you won't have a fetch-over-commit situation.

An AUTONOMOUS TRANSACTION will indeed give the same behaviour, but as you didn't mention the version your are working with....
and you could ask yourself if you don't alter the behaviour
of the rest of your application by making this commit an autonomous one. What other procedures are dependent on this commit?
0
 
LVL 6

Expert Comment

by:venkotch
ID: 6968312
Just to clarify sora's comment.
You do not have the option not using FOR UPDATE. Just becasue you are updating the data which is used in cursor's where clause. So either (as Pennn said) use the pragma AUTONOMOUS_TRANSACTION or remove the commit from the stored procedure and issue COMMIT after the cursor loop. However, if your stored proc is dealing with the same data, used by the cursor - the pragma will not help.
One more thing - I would prefer to use FOR CURSOR loop instead of OPEN - FETCH - CLOSE:

 FOR regis_rec IN regis_cur LOOP
    total_record_count := total_record_count + 1;  
    SELECT COUNT(*)
       INTO regis_zone_count
       FROM siebel.s_lst_of_val
       WHERE VAL = regis_rec.zone AND TYPE = 'ZONE' ;
    IF regis_zone_count != 1 THEN
       UPDATE regis_import_zones
          SET exported_flag = 'E'
        WHERE CURRENT OF regis_cur ;
    END IF ;
 END LOOP;
0
 

Author Comment

by:pardeep_nagra
ID: 6971671
Cheers Sora !!
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6971811
Pardeep,
   If the answer you got from sora was complete, is there a reason you gave it a grade of B?  Remember that others, searching for answers, might also benfit from sora's answer, but they're not likely to look at it if the grade isn't an A.  You don't want to grade based on whether or not you LIKE the answer and you should try to use the grade to adjust for difficulty (that's the role of the points).  Whenever you give an answer other than an A, please take the time to explain you reasoning.  We'll all benefit from the feedback.  Thanks.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question