Topic: ORA-01002: Fetch out of sequence

Source: Internet
Author: User

Official explanation:

ORA-01002: Fetch out of sequence
Cause: In a host language program, a fetch call was issued out of sequence. A successful parse-and-Execute call must be issued before a fetch. this can occur if an attempt was made to fetch from an active set after all records have been fetched. this may be 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:
1. A commit or rollback statement may exist during the process of obtaining and executing part of the data. As a result, the lock on table t is released and an error occurs when the data is retrieved.

Fetching processing SS commits
The for update clause acquires exclusive row locks. All rows are locked when 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 do, PL/SQL raises
Exception. In the following example, the cursor for 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, 'Kill goin ');
If CTR> = 10 then
Commit; -- releases locks
End if;

End loop;

If you want to fetch your SS commits, do not use the for update and current
Clures. Instead, use the rowid pseudo column to mimic the current of clause.
Simply select the rowid of each row into a urowid variable. Then, use the rowid
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;
Open C1;
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 Automatic submission. If you still have other rows that are disabled for manual submission during query, any submission executed when the for update cursor is still open may cause this error.
Setautocommit (false)

Actual Example:

In fact, one of our customers is using Oracle 9.2 as the backend. He wants to retrieve data from the table using a simple select (select * from State) statement. He got an error message: "ORA-01002: Fetch out of sequence ". At the same time, I also use SQL * Plus on my computer and front-end. I use both of them to work well. Only one simple SELECT statement is used to obtain the error information of this type. Why?
Are you sure he hasn't used the SELECT statement as the pointer? In general, if you try to execute a commit Statement on the data rows being retrieved by the SQL statement without specifying the for update clause, this error will occur, in another case, if you execute this statement again after the last row is retrieved by the pointer, this error message will also appear (in this case, according to your actual situation, pointer is defined as 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.