Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70768
  • Last Modified:

Cursor ORA-01002: fetch out of sequence

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
pardeep_nagra
Asked:
pardeep_nagra
  • 2
  • 2
  • 2
  • +4
1 Solution
 
pennnnCommented:
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
 
pardeep_nagraAuthor Commented:
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
 
DrSQLCommented:
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
Technology Partners: 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!

 
pennnnCommented:
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
 
soraCommented:
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
 
kretzschmarCommented:
i got this error, if the rollback-segment is too small
0
 
vanmeerendonkCommented:
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
 
venkotchCommented:
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
 
pardeep_nagraAuthor Commented:
Cheers Sora !!
0
 
DrSQLCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now