ORA-01002: fetch out of sequence

I got ” ORA-01002: fetch out of sequence ” error in Oracle database.

 

ORA-01002: fetch out of sequence

 

Details of error are as follows.

 

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.

 

 

 fetch out of sequence

This ORA-01002 errors are related with the 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.

 

To solve this error, apply the following steps.

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.

 

To solve this error, Don’t use a FETCH on a SELECT FOR UPDATE after a COMMIT has been issued. Use cursor attributes to avoid these situations.

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )