Oracle ORA-01002 Error: Causes and Solutions Complete Guide

ORA-01002: fetch out of sequence — Causes, Fixes & Prevention

ORA-01002 occurs when Oracle detects a FETCH operation on a cursor that is in an invalid or out-of-sequence state. The most common trigger is fetching from a cursor that has been implicitly invalidated — typically because a COMMIT or ROLLBACK was issued inside a FOR UPDATE cursor loop. This error appears frequently in PL/SQL, Pro*C, JDBC, and OCI-based applications where cursors are managed explicitly.

---

Top 3 Causes

1. COMMIT or ROLLBACK Inside a FOR UPDATE Cursor Loop

This is the #1 cause in production environments. When you open a SELECT ... FOR UPDATE cursor and issue a COMMIT or ROLLBACK inside the fetch loop, Oracle immediately invalidates the cursor. Any subsequent FETCH triggers ORA-01002.

-- BAD: COMMIT inside FOR UPDATE cursor loop
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        FOR UPDATE OF salary;
    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;

        UPDATE employees
        SET salary = v_emp.salary * 1.1
        WHERE CURRENT OF c_emp;

        COMMIT; -- Cursor is invalidated here → next FETCH raises ORA-01002
    END LOOP;
    CLOSE c_emp;
END;
/

2. Fetching from an Already Closed Cursor

Attempting to FETCH from a cursor after it has been explicitly CLOSEd — or after an unhandled exception has caused the cursor to close implicitly — will raise ORA-01002.

-- BAD: FETCH after CLOSE
DECLARE
    CURSOR c_dept IS
        SELECT department_id, department_name
        FROM departments;
    v_dept c_dept%ROWTYPE;
BEGIN
    OPEN c_dept;
    FETCH c_dept INTO v_dept;
    CLOSE c_dept;          -- Cursor closed here

    FETCH c_dept INTO v_dept;  -- ORA-01002 raised here
END;
/

3. Fetching from an Unopened REF CURSOR

In dynamic SQL or REF CURSOR scenarios, attempting a FETCH before the cursor is opened results in ORA-01002. This often happens when a cursor variable is passed between procedures and the calling code assumes it is already open.

-- BAD: FETCH without OPEN on a REF CURSOR
DECLARE
    TYPE ref_cur_type IS REF CURSOR;
    v_cur  ref_cur_type;
    v_name VARCHAR2(100);
BEGIN
    -- Missing OPEN statement
    FETCH v_cur INTO v_name;  -- ORA-01002 raised immediately
END;
/

---

Quick Fix Solutions

Fix for Cause 1 — Move COMMIT outside the loop:

-- GOOD: Single COMMIT after the loop completes
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        FOR UPDATE OF salary;
    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;

        UPDATE employees
        SET salary = v_emp.salary * 1.1
        WHERE CURRENT OF c_emp;
    END LOOP;
    CLOSE c_emp;
    COMMIT;  -- Safe: cursor is already closed
END;
/

Fix for Cause 2 — Use %ISOPEN and proper EXCEPTION handling:

-- GOOD: Always check cursor state and handle exceptions
DECLARE
    CURSOR c_dept IS
        SELECT department_id, department_name FROM departments;
    v_dept c_dept%ROWTYPE;
BEGIN
    IF NOT c_dept%ISOPEN THEN
        OPEN c_dept;
    END IF;

    LOOP
        FETCH c_dept INTO v_dept;
        EXIT WHEN c_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_dept.department_name);
    END LOOP;

    CLOSE c_dept;
EXCEPTION
    WHEN OTHERS THEN
        IF c_dept%ISOPEN THEN
            CLOSE c_dept;  -- Always close on error
        END IF;
        RAISE;
END;
/

Fix for Cause 3 — Always OPEN before FETCH:

-- GOOD: Proper REF CURSOR lifecycle
DECLARE
    TYPE ref_cur_type IS REF CURSOR;
    v_cur  ref_cur_type;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN v_cur FOR
        SELECT first_name FROM employees WHERE department_id = 20;

    LOOP
        FETCH v_cur INTO v_name;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;

    CLOSE v_cur;
END;
/

---

Prevention Tips

• Never COMMIT or ROLLBACK inside a FOR UPDATE cursor loop. If you need incremental commits for large datasets, refactor using BULK COLLECT / FORALL to eliminate the cursor loop entirely — this also delivers significant performance gains.
• Always implement an EXCEPTION block that closes open cursors. Add a checklist item to your code review process: every explicit cursor must have a matching CLOSE in both the normal flow and the EXCEPTION handler, verified using %ISOPEN before closing.

---

Related Errors

Error Code	Description
ORA-01001	Invalid cursor — referencing an invalid cursor handle
ORA-06511	PL/SQL cursor already open — re-opening an open cursor
ORA-01003	No statement parsed — executing a DBMS_SQL cursor without parsing

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.