ORA-01002: Fetch Out of Sequence - Fix Cursor Errors

ORA-01002: Fetch Out of Sequence

Error Overview

Error Text: ORA-01002: fetch out of sequence

The ORA-01002 error is raised when a FETCH operation is attempted against a cursor that is in an invalid state for fetching. The most common trigger is issuing a COMMIT or ROLLBACK inside a cursor loop when the cursor was opened with FOR UPDATE, which invalidates the cursor’s position in the result set. It also occurs when fetching after the cursor has already returned %NOTFOUND, or when reopening a cursor without closing it first.

Common Causes

1. COMMIT Inside a FOR UPDATE Cursor Loop

• The FOR UPDATE clause locks rows and ties cursor position to the transaction
• Issuing COMMIT releases locks and invalidates the cursor, causing ORA-01002 on the next fetch
• This is the most frequent cause in production PL/SQL

2. ROLLBACK Invalidating an Open Cursor

• A ROLLBACK inside a cursor loop similarly releases FOR UPDATE locks and invalidates cursor position
• Implicit rollbacks from unhandled exceptions inside a fetch loop have the same effect

3. Fetching After %NOTFOUND Is TRUE

• Continuing to fetch from a cursor after %NOTFOUND returns TRUE raises ORA-01002
• This typically indicates a missing EXIT WHEN condition in a manual OPEN/FETCH/CLOSE loop

4. Fetching from a Closed or Never-Opened Cursor

• Attempting FETCH before OPEN or after CLOSE raises ORA-01002
• Conditional OPEN logic where the cursor may not have been opened on a specific code path

5. DDL Statement Inside a Cursor Loop

• DDL statements perform an implicit COMMIT, invalidating any open FOR UPDATE cursors

Diagnostic Queries

Identify Sessions with Open Cursors

-- Check open cursor counts per session
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.module,
  c.cursor_count
FROM v$session s
JOIN (
  SELECT sid, COUNT(*) AS cursor_count
  FROM v$open_cursor
  GROUP BY sid
) c ON s.sid = c.sid
WHERE c.cursor_count > 50
ORDER BY c.cursor_count DESC;

Find FOR UPDATE Cursors in Active Sessions

-- Locate sessions with FOR UPDATE cursor locks
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.sql_id,
  q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE UPPER(q.sql_text) LIKE '%FOR UPDATE%'
  AND s.status = 'ACTIVE';

Check Row-Level Locks Held by Sessions

-- Sessions holding row-level locks (from FOR UPDATE)
SELECT
  l.sid,
  s.serial#,
  s.username,
  l.type,
  l.lmode,
  l.request,
  l.block
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
WHERE l.type = 'TM'
ORDER BY l.sid;

Check for ORA-01002 in Audit Trail

SELECT
  event_timestamp,
  db_user_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 1002
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY event_timestamp DESC;

Step-by-Step Resolution

1. Fix COMMIT Inside a FOR UPDATE Cursor Loop

The root cause fix is to not issue COMMIT until after the cursor is closed:

-- WRONG: COMMIT inside FOR UPDATE cursor loop
-- DECLARE
--   CURSOR c_emp IS
--     SELECT employee_id, salary
--     FROM employees
--     FOR UPDATE;
-- BEGIN
--   FOR r IN c_emp LOOP
--     UPDATE employees SET salary = r.salary * 1.1
--     WHERE CURRENT OF c_emp;
--     COMMIT;  -- ORA-01002 on next iteration
--   END LOOP;
-- END;

-- CORRECT: Commit after the loop
DECLARE
  CURSOR c_emp IS
    SELECT employee_id, salary
    FROM employees
    FOR UPDATE;
BEGIN
  FOR r IN c_emp LOOP
    UPDATE employees SET salary = r.salary * 1.1
    WHERE CURRENT OF c_emp;
  END LOOP;
  COMMIT;  -- Single commit after all updates
END;
/

2. Batch Processing Alternative When Mid-Loop Commits Are Required

When rows must be committed in batches (e.g., very large tables):

-- Use BULK COLLECT with a LIMIT to process in chunks
DECLARE
  TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
  l_ids emp_id_tab;

  CURSOR c_emp IS
    SELECT employee_id FROM employees WHERE salary < 50000;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO l_ids LIMIT 500;
    EXIT WHEN l_ids.COUNT = 0;

    FORALL i IN 1..l_ids.COUNT
      UPDATE employees
      SET salary = salary * 1.1
      WHERE employee_id = l_ids(i);

    COMMIT;  -- Safe to commit here — no FOR UPDATE cursor open
  END LOOP;
  CLOSE c_emp;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE c_emp;
    ROLLBACK;
    RAISE;
END;
/

3. Fix Manual OPEN/FETCH/CLOSE Loop with Missing EXIT

-- WRONG: Fetching past %NOTFOUND
-- DECLARE
--   CURSOR c1 IS SELECT employee_id FROM employees;
--   v_id employees.employee_id%TYPE;
-- BEGIN
--   OPEN c1;
--   LOOP
--     FETCH c1 INTO v_id;
--     -- Missing EXIT WHEN c1%NOTFOUND here!
--     process_employee(v_id);
--   END LOOP;
--   CLOSE c1;
-- END;

-- CORRECT: Always check %NOTFOUND immediately after FETCH
DECLARE
  CURSOR c1 IS SELECT employee_id FROM employees;
  v_id employees.employee_id%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_id;
    EXIT WHEN c1%NOTFOUND;  -- Must be immediately after FETCH
    process_employee(v_id);
  END LOOP;
  CLOSE c1;
EXCEPTION
  WHEN OTHERS THEN
    IF c1%ISOPEN THEN CLOSE c1; END IF;
    RAISE;
END;
/

4. Fix Implicit Rollback from Exception Handler

-- Pattern to avoid implicit cursor invalidation from exceptions
DECLARE
  CURSOR c_emp IS
    SELECT employee_id, salary FROM employees FOR UPDATE;
  v_count NUMBER := 0;
BEGIN
  FOR r IN c_emp LOOP
    BEGIN
      UPDATE employees SET salary = r.salary * 1.05
      WHERE CURRENT OF c_emp;
      v_count := v_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        -- Log the error but do NOT rollback inside the loop
        INSERT INTO error_log (emp_id, error_msg, log_time)
        VALUES (r.employee_id, SQLERRM, SYSDATE);
        -- Continuing without ROLLBACK preserves cursor validity
    END;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' rows');
END;
/

5. Fix Cursor State Check Before Fetching

-- Always guard FETCH with cursor state checks
DECLARE
  CURSOR c1 IS SELECT employee_id, last_name FROM employees WHERE rownum <= 10;
  v_rec c1%ROWTYPE;
BEGIN
  IF NOT c1%ISOPEN THEN
    OPEN c1;
  END IF;

  LOOP
    FETCH c1 INTO v_rec;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.employee_id || ' - ' || v_rec.last_name);
  END LOOP;

  IF c1%ISOPEN THEN
    CLOSE c1;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    IF c1%ISOPEN THEN CLOSE c1; END IF;
    RAISE;
END;
/

Prevention Strategies

1. Cursor Management Best Practices

-- Standard cursor handling template for FOR UPDATE scenarios
DECLARE
  CURSOR c_target IS
    SELECT id, value FROM target_table
    WHERE condition = 'Y'
    FOR UPDATE NOWAIT;  -- NOWAIT prevents indefinite blocking
BEGIN
  FOR r IN c_target LOOP
    -- Perform DML using WHERE CURRENT OF (most efficient)
    UPDATE target_table
    SET value = r.value + 1, updated_date = SYSDATE
    WHERE CURRENT OF c_target;
    -- DO NOT COMMIT inside this loop
  END LOOP;
  COMMIT;  -- Single commit after loop
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

2. Prefer FOR Loops Over OPEN/FETCH/CLOSE

-- Implicit cursor FOR loops manage cursor lifecycle automatically
-- ORA-01002 cannot occur from missing EXIT WHEN when using FOR syntax
BEGIN
  FOR r IN (SELECT employee_id, salary FROM employees WHERE department_id = 50) LOOP
    DBMS_OUTPUT.PUT_LINE(r.employee_id || ': ' || r.salary);
  END LOOP;
  -- Cursor is automatically closed at loop exit
END;
/

3. Monitor Cursor Usage

-- Schedule a cursor health check
SELECT
  s.username,
  s.program,
  COUNT(*) AS open_cursors
FROM v$open_cursor oc
JOIN v$session s ON oc.sid = s.sid
GROUP BY s.username, s.program
HAVING COUNT(*) > 100
ORDER BY open_cursors DESC;

4. Set CURSOR_SHARING and OPEN_CURSORS Appropriately

-- Check current cursor limits
SHOW PARAMETER open_cursors;
SHOW PARAMETER cursor_sharing;

-- Adjust if sessions routinely hit cursor limits
ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH;

Related Errors

• ORA-01555 - Snapshot too old (related fetch issue)
• ORA-06512 - At line (PL/SQL error stack)
• ORA-04091 - Table is mutating
• ORA-01000 - Maximum open cursors exceeded

Emergency Response

Quick Fixes

1. Kill the offending session if it is blocking

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. Check if FOR UPDATE locks are blocking other sessions

   SELECT
     l.sid, s.username, s.program,
     'Lock holder' AS role
   FROM v$lock l
   JOIN v$session s ON l.sid = s.sid
   WHERE l.type = 'TX' AND l.lmode = 6;

3. Identify and close leaked open cursors

   SELECT sid, user_name, sql_text
   FROM v$open_cursor
   WHERE user_name = 'YOUR_USER'
   ORDER BY sid;

Post-Resolution Cleanup

-- Verify no stale locks after session kill or fix
SELECT COUNT(*) FROM v$lock WHERE type = 'TX';

-- Confirm cursor count returns to normal
SELECT COUNT(*) FROM v$open_cursor;

-- Recompile affected packages
ALTER PACKAGE affected_package COMPILE BODY;

-- Run regression test for the affected procedure
EXEC test_procedure_name;