Topic: Ora-01002:fetch Out of sequence issues

Source: Internet
Author: User

Official explanation:

ORA-01002: Fetch out of sequence
Cause: In a host language program, A is FETCH call is issued out of sequence. A successful Parse-and-execute call must is issued before a fetch. This can occur if a attempt is made to FETCH from a active set after all records have been. This May is caused by fetching from a SELECT for UPDATE cursor after a commit. A Pl/sql cursor loop implicitly does fetches and may also cause this error.
Action: Parse and execute a SQL statement before attempting to fetch the data.
Practical application and Solution method: 
1, after you take part of the data and execution of the process, there may be a commit or rollback statements, resulting in the table T plus lock is released, and then the data will cause an error. 

Fetching across commits 
The FOR UPDATE clause acquires exclusive row locks. All rows are locked if you 
Open the cursor, and they are unlocked when you commit your transaction. So, you 
Cannot fetch from a for UPDATE cursor after a commit. If you did, Pl/sql raises an 
exception. In the following example, the cursor to loop fails after the tenth insert: 

CURSOR C1 is SELECT ename from EMP for UPDATE of Sal; 
CTR Number: = 0; 
For Emp_rec in C1 loop--fetches implicitly 
CTR: = ctr + 1; 
INSERT into temp VALUES (CTR, ' still going '); 
IF Ctr >= THEN 
COMMIT; --Releases locks 
End IF; 

End LOOP; 

If you are want to fetch across commits, does not use the ' for UPDATE ' and ' 
Clauses. Instead, use the "ROWID pseudocolumn to mimic" the current of clause. 
Simply Select the rowid of each row into a Urowid variable. Then, use the ROWID to 
Identify the current row during subsequent updates and deletes. An example

CURSOR C1 is SELECT ename, job, rowid from EMP; 
My_ename Emp.ename%type; 
My_job Emp.job%type; 
My_rowid Urowid; 
FETCH C1 into My_ename, My_job, My_rowid; 
EXIT when C1%notfound; 
UPDATE emp SET sal = sal * 1.05 WHERE rowid = My_rowid; 
--This mimics WHERE current of C1 
End LOOP; 
Close C1; 
2, disable the automatic submission to try. If you still have other rows in the query that also disable manual submission, any submissions that are executed when a for update cursor is still open may cause this error. 
Setautocommit (False) 
Actual case:
In fact, one of our customers is using Oracle 9.2 as the backend. He wants to retrieve data from a table using a simple select (SELECT * from) statement from a table. He got an error message: "Ora-01002:fetch out of sequence". At the same time, I also use sql*plus on my computer, as well as the front end. I use both of these to work well. The reason for this type of error message is to use only a simple SELECT statement. 
Are you sure he didn't use the SELECT statement as part of the pointer? Typically, if you do not specify a FOR UPDATE clause, this error occurs when an attempt is made to execute a commit on the row of data that the SQL statement is retrieving, and in another case, this error message occurs when you execute the statement again after the last line is retrieved by the pointer (in which case , according to your actual situation, the pointer is defined as a select * from state.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.