Home » SQL & PL/SQL » SQL & PL/SQL » Fetch out of sequence error.
Fetch out of sequence error. [message #272234] Thu, 04 October 2007 05:01 Go to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Hi All,

I am having a problem with my procedure, it is throwing out error fetch out of sequence.

here is the code.

DECLARE
 cursor...
BEGIN
 for loop..
BEGIN

   if ...
   exception validation_exception
    
EXCEPTION
  WHEN  validation_exception THEN
    ROLLBACK;
 
    UPDATE ......
    COMMIT;
 
  WHEN OTHERS THEN
   UPDATE .....
   COMMIT;
END;
END LOOP;
END;


what area this is causing the problem..

thx
Sai
Re: Fetch out of sequence error. [message #272240 is a reply to message #272234] Thu, 04 October 2007 05:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please have a look at OraFAQ Forum Guide.
If you can put some effort in posting your full code and the errors (with error code and line number), then you might get some help.

By
Vamsi
Re: Fetch out of sequence error. [message #272242 is a reply to message #272240] Thu, 04 October 2007 05:22 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

ya sure.. this is how my procedure is
Create or replace procedure p1
is
    cursor c1 is select * 
    from table1
    where status ='WAITING'
    order by seq_id;

 begin

  for rec in c1 loop

begin

    select count(1) 
    into xyx 
    from table1
    where status ='ERROR'

   if xyz<>0
   then
   raise validation_exception
   end if;

    select ....
     update ...

  EXCEPTION
   WHEN  validation_exception THEN
  ROLLBACK;

  update table1
  set status = 'ERROR'
  ,reason='VALIDATION'
  ....
  commit;

  WHEN OTHERS then

  update table1 
  set satutus = 'ERROR'
  ,reason = 'OTHERS'
  ...
  commit;
 exit;
end;
end loop;
end p1;
Re: Fetch out of sequence error. [message #272250 is a reply to message #272234] Thu, 04 October 2007 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01002: "fetch out of sequence"
 *Cause: This error means that a fetch has been attempted from a cursor
         which is no longer valid.  Note that a PL/SQL cursor loop
         implicitly does fetches, and thus may also cause this error.
         There are a number of possible causes for this error, including:
         1) Fetching from a cursor after the last row has been retrieved
            and the ORA-1403 error returned.
         2) If the cursor has been opened with the FOR UPDATE clause,
            fetching after a COMMIT has been issued will return the error.
         3) Rebinding any placeholders in the SQL statement, then issuing
            a fetch before reexecuting the statement.
 *Action: 1) Do not issue a fetch statement after the last row has been
             retrieved - there are no more rows to fetch.
          2) Do not issue a COMMIT inside a fetch loop for a cursor
             that has been opened FOR UPDATE.
          3) Reexecute the statement after rebinding, then attempt to
             fetch again.

Regards
Michel
Re: Fetch out of sequence error. [message #272609 is a reply to message #272234] Fri, 05 October 2007 13:12 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I don't wish to be rude, but I must agree with previos responder. If you are not willing to send us real code, you should not expect an answer. I get very annoyed when someone post and "example" which is clearly invalid.

Allowing for your abbreviations of sql (...), the rest of the plsql code is riddled with syntanx errors and ommissions. I for one am loathe to even look at it. Shame on you.

Even aside from these issues, there are errors in your code. For example your singlton select is an aggregious codeing error. what makes you this there will be a row in the table of type ERROR?

Lastly, I don't see an error message anywhere. You should cut/paste the message you are getting.

Please post full code along with actual error message. Your psuedo code helps but not without the real code. And make sure what you are suppling compiles without syntax errors. You can feel free to attach a file if what you have is large.

Kevin Meade


Previous Topic: A way to check if a string is infact a number
Next Topic: detailed datafile size
Goto Forum:
  


Current Time: Sat Dec 10 08:47:18 CST 2016

Total time taken to generate the page: 0.07616 seconds