Solved

Cursor ORA-01002: fetch out of sequence

Posted on 2002-04-24
10
70,645 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
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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 96
C# Connection String for Oracle database is not working 22 143
Oracle Listener Not Starting 11 44
Trying to get a Linked Server to Oracle DB working 21 60
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

777 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