Oracle ORA-01001 Error: Causes and Solutions Complete Guide

ORA-01001: Invalid Cursor — Causes, Fixes, and Prevention

ORA-01001 is thrown by Oracle when your PL/SQL code attempts to use a cursor that is no longer valid — typically because it has already been closed, was never opened, or references a NULL cursor variable. This error is one of the most common PL/SQL runtime errors and almost always points to a cursor lifecycle management issue in your code.

---

Top 3 Causes

1. Fetching from or Closing an Already-Closed Cursor

The most frequent cause: calling CLOSE or FETCH on a cursor that was already closed — especially inside EXCEPTION blocks where the cursor may have been closed before the exception was raised.

DECLARE
  CURSOR emp_cur IS SELECT employee_id FROM employees;
  v_id employees.employee_id%TYPE;
BEGIN
  OPEN emp_cur;
  FETCH emp_cur INTO v_id;
  CLOSE emp_cur;

  -- ORA-01001 triggered here: cursor is already closed
  CLOSE emp_cur;
END;
/

Fix: Always check %ISOPEN before closing.

IF emp_cur%ISOPEN THEN
  CLOSE emp_cur;
END IF;

---

2. Fetching from a Cursor That Was Never Opened

When conditional logic prevents a cursor from being opened, but the FETCH statement runs anyway, ORA-01001 is raised.

DECLARE
  CURSOR dept_cur IS SELECT department_id FROM departments;
  v_id departments.department_id%TYPE;
  v_flag BOOLEAN := FALSE;
BEGIN
  IF v_flag THEN
    OPEN dept_cur; -- This never executes
  END IF;

  -- ORA-01001: cursor was never opened
  FETCH dept_cur INTO v_id;
END;
/

Fix: Guard every FETCH with an %ISOPEN check.

IF dept_cur%ISOPEN THEN
  FETCH dept_cur INTO v_id;
END IF;

---

3. Using an Uninitialized or NULL REF CURSOR

When a SYS_REFCURSOR variable is passed between procedures but never properly opened with OPEN FOR, fetching from it causes ORA-01001.

DECLARE
  v_cur SYS_REFCURSOR;
  v_id  NUMBER;
BEGIN
  -- v_cur is never opened — ORA-01001 on FETCH
  FETCH v_cur INTO v_id;
  CLOSE v_cur;
END;
/

Fix: Always open a REF CURSOR before fetching, and handle exceptions safely.

DECLARE
  v_cur    SYS_REFCURSOR;
  v_id     employees.employee_id%TYPE;
  v_name   employees.first_name%TYPE;
BEGIN
  OPEN v_cur FOR
    SELECT employee_id, first_name
    FROM employees
    WHERE department_id = 10;

  LOOP
    FETCH v_cur INTO v_id, v_name;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
  END LOOP;

  IF v_cur%ISOPEN THEN
    CLOSE v_cur;
  END IF;

EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('ORA-01001: Invalid cursor detected.');
    IF v_cur%ISOPEN THEN
      CLOSE v_cur;
    END IF;
END;
/

---

Quick Fix Checklist

Scenario	Fix
Double CLOSE	Add IF cursor%ISOPEN THEN CLOSE guard
FETCH before OPEN	Check %ISOPEN before every FETCH
NULL REF CURSOR	Always use OPEN FOR before FETCH
Complex exception paths	Add cursor cleanup in every EXCEPTION block

---

Prevention Tips

1. Prefer Cursor FOR Loops
Use implicit FOR loops whenever possible. Oracle automatically handles OPEN and CLOSE, eliminating ORA-01001 risk entirely.

BEGIN
  FOR rec IN (SELECT employee_id, first_name FROM employees)
  LOOP
    DBMS_OUTPUT.PUT_LINE(rec.employee_id || ': ' || rec.first_name);
  END LOOP;
  -- No manual OPEN/CLOSE needed
END;
/

2. Always Clean Up Cursors in EXCEPTION Blocks
Make it a coding standard to include %ISOPEN-guarded CLOSE calls in every EXCEPTION section. Add this to your code review checklist to catch issues before they reach production.

---

Related Errors

• ORA-01000 — Maximum open cursors exceeded (too many unclosed cursors)
• ORA-06511 — Cursor already open (opposite problem: opening an already-open cursor)
• ORA-01002 — Fetch out of sequence (cursor invalidated after COMMIT in FOR UPDATE context)

---

📖 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.