Solved

Cursor ORA-01002: fetch out of sequence

Posted on 2002-04-24
10
70,597 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
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 11

Expert Comment

by:pennnn
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:pennnn
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Expert Comment

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

Expert Comment

by:vanmeerendonk
Comment Utility
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
Comment Utility
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
Comment Utility
Cheers Sora !!
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now