In Oracle databases, the ORA-01002: Fetch Out of Sequence error occurs when a FETCH operation is attempted on a cursor that is no longer valid. This error is typically encountered in PL/SQL when dealing with explicit cursors or SELECT FOR UPDATE statements.
This topic explores the causes, solutions, and best practices for handling the ORA-01002 error effectively.
Understanding ORA-01002: Fetch Out of Sequence
What Does ORA-01002 Mean?
This error indicates that a FETCH operation was attempted in an improper sequence or on an invalid cursor state. The database expects a certain order of operations, and if this order is violated, ORA-01002 is triggered.
Common Scenarios Where ORA-01002 Occurs
- Using explicit cursors incorrectly.
- Attempting to FETCH from a closed or improperly handled cursor.
- Working with SELECT FOR UPDATE queries and committing before fetching all rows.
- Re-executing a query without properly reopening the cursor.
Causes of ORA-01002
1. FETCH After COMMIT or ROLLBACK
In SELECT FOR UPDATE queries, if a COMMIT or ROLLBACK occurs before all rows are fetched, Oracle invalidates the cursor.
Example:
DECLARECURSOR emp_cursor IS SELECT * FROM employees FOR UPDATE;emp_record employees%ROWTYPE;BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;COMMIT; -- This invalidates the cursorFETCH emp_cursor INTO emp_record; -- ORA-01002 error occurs hereEND;Solution:
Avoid committing within a fetch loop. Move COMMIT to the end of processing:
BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;-- Process record hereEND LOOP;CLOSE emp_cursor;COMMIT; -- Commit after processingEND;2. Fetching from a Closed Cursor
A cursor must be open before fetching. If a cursor is closed before fetching all rows, ORA-01002 will occur.
Example:
DECLARECURSOR emp_cursor IS SELECT * FROM employees;emp_record employees%ROWTYPE;BEGINOPEN emp_cursor;CLOSE emp_cursor; -- Cursor is closedFETCH emp_cursor INTO emp_record; -- Error occurs hereEND;Solution:
Ensure that the cursor is open before fetching.
BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;CLOSE emp_cursor;END;3. Using FETCH Without OPEN
Before fetching, the cursor must be explicitly opened.
Example:
DECLARECURSOR emp_cursor IS SELECT * FROM employees;emp_record employees%ROWTYPE;BEGINFETCH emp_cursor INTO emp_record; -- ORA-01002 error, cursor not openedEND;Solution:
Always open the cursor before fetching:
BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;CLOSE emp_cursor;END;4. Fetching After Query Re-Execution Without Closing Cursor
When a query is re-executed, the cursor must be explicitly closed and reopened.
Example:
DECLARECURSOR emp_cursor IS SELECT * FROM employees;emp_record employees%ROWTYPE;BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;OPEN emp_cursor; -- Error: Cursor already openFETCH emp_cursor INTO emp_record; END;Solution:
Before re-executing a query, close the cursor first.
BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;CLOSE emp_cursor; -- Close before re-openingOPEN emp_cursor;FETCH emp_cursor INTO emp_record;END;How to Fix ORA-01002: Best Practices
1. Manage Cursors Properly
- Always open a cursor before fetching.
- Close the cursor after fetching all rows.
- Do not fetch after COMMIT or ROLLBACK in SELECT FOR UPDATE queries.
2. Use Implicit Cursors When Possible
Implicit cursors handle opening and closing automatically, reducing errors.
Example of Implicit Cursor:
BEGINFOR emp_record IN (SELECT * FROM employees) LOOP-- Process each row hereDBMS_OUTPUT.PUT_LINE(emp_record.name);END LOOP;END;3. Use Cursor Attributes for Safety
Cursor attributes help check the state of the cursor before fetching.
Common Cursor Attributes:
| Attribute | Description |
|---|---|
%FOUND |
TRUE if a row was fetched successfully |
%NOTFOUND |
TRUE if no more rows are available |
%ISOPEN |
TRUE if the cursor is open |
%ROWCOUNT |
Number of rows fetched so far |
Example:
BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;-- Process rowEND LOOP;CLOSE emp_cursor;END;4. Avoid COMMIT in Fetch Loops
In SELECT FOR UPDATE queries, a COMMIT before fetching all rows invalidates the cursor. Always commit after processing.
5. Use Exception Handling for Safety
Exception handling ensures graceful error recovery.
Example:
BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);IF emp_cursor%ISOPEN THENCLOSE emp_cursor;END IF;END;Common Errors Related to ORA-01002
| Error Code | Cause | Solution |
|---|---|---|
| ORA-01001 | Invalid cursor | Ensure the cursor is declared and opened before use. |
| ORA-06502 | Numeric value error | Check for data type mismatches when fetching values. |
| ORA-01403 | No data found | Ensure fetch conditions match existing data. |
The ORA-01002: Fetch Out of Sequence error occurs due to improper cursor handling, especially when fetching data after COMMIT, closing a cursor too early, or skipping cursor opening.
Key Takeaways:
✔ Always open a cursor before fetching.
✔ Avoid committing inside fetch loops.
✔ Close cursors properly after fetching all rows.
✔ Use cursor attributes to track cursor state.
✔ Handle exceptions to avoid unexpected failures.
By following these best practices, you can prevent ORA-01002 errors and optimize your Oracle database operations efficiently.