oracle ORA-01002: fetch out of sequence tips 违反提取顺序

The Oracle oerr utility  notes this on the ora-01002 error:

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 re-executing 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) Re-execute the statement after rebinding, then attempt to fetch again.

ORA-01002 can have multiple causes including:

• A PL/SQL loop does fetches without notice
• Attempting to fetch from a cursor that is no longer valid (fetching from a row which has been retrieved).
• Fetching after a COMMIT has already been issued and a cursor is opened with the FOR UPDATE clause.
• Issuing a fetch before re-executing a SQL after rebinding placeholders.

You may want to try using cursor attributes to dodge ORA-01002 in the future. To resolve a current ORA-01002, there are three actions you can perform:

1. After the last record is received, do not issue a fetch
2. Inside a fetch loop on a SELECT FOR UPDATE, do not use a COMMIT
3. Try fetching again after re-executing the statement (after rebinding)