Home » SQL & PL/SQL » SQL & PL/SQL » Fetch out of sequence error .... (Oracle 10g, Sun Solaris 5.9)
Fetch out of sequence error .... [message #288214] Sat, 15 December 2007 22:25 Go to next message
Messages: 11
Registered: December 2005
Location: Chennai
Junior Member

My stored proc opens a cursor accorss gateway (oracle Gateway 9i version) to Mainframe DB2 database. After opening cursor, it delets some records in Oracle datase (inside cursor loop). This simple FOR cursor loop. Proc is rus for a while and then fails with the error "Fetch Out of Sequence". The same code was working fine on Oracle 9i. I'm not finding anything in the proc that would cause this problem. I also checked all the sequences. They all look fine.
Is this due to compatiblity issues b/w Oracle 10g and 9i Gateway?? Please suggest.

Re: Fetch out of sequence error .... [message #288224 is a reply to message #288214] Sat, 15 December 2007 23:18 Go to previous message
Barbara Boehmer
Messages: 8737
Registered: November 2002
Location: California, USA
Senior Member
It might help if you post your code. One of us might see something that you missed. Otherwise, about all that we can provide is the general information from the documentaiton:

"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."
Previous Topic: NO DATA Exception
Next Topic: group by & having clause
Goto Forum:

Current Time: Tue Aug 22 08:06:46 CDT 2017

Total time taken to generate the page: 0.05603 seconds