Oracle ORA-01023 Error: Causes and Solutions Complete Guide

ORA-01023: Cursor Context Not Found (Invalid Cursor Number)

ORA-01023 occurs when Oracle cannot locate a valid cursor context — typically because the cursor has already been closed, was never properly opened, or the cursor number being referenced is invalid. This error appears across PL/SQL blocks, Pro*C applications, OCI programs, and JDBC-based applications whenever cursor lifecycle management is mishandled.

---

Top 3 Causes and Fixes

Cause 1: Accessing a Cursor After It Has Been Closed

The most common trigger is attempting a FETCH or reading cursor attributes (%ROWCOUNT, %FOUND) after the cursor has already been explicitly closed.

-- BAD: Causes ORA-01023
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;
    FETCH emp_cur INTO v_id; -- ORA-01023 triggered here!
END;
/

-- GOOD: Always check %ISOPEN before accessing
DECLARE
    CURSOR emp_cur IS SELECT employee_id FROM employees;
    v_id employees.employee_id%TYPE;
BEGIN
    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO v_id;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id);
    END LOOP;

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

Cause 2: Mismanaging Cursor Numbers with DBMS_SQL

When using the DBMS_SQL package for dynamic SQL, reusing a cursor number after calling CLOSE_CURSOR, or failing to check IS_OPEN before operations, leads directly to ORA-01023.

-- GOOD: Safe DBMS_SQL cursor management
DECLARE
    v_cur    INTEGER;
    v_sql    VARCHAR2(200) := 'SELECT employee_id FROM employees WHERE rownum = 1';
    v_result INTEGER;
    v_emp_id NUMBER;
BEGIN
    v_cur := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_emp_id);

    v_result := DBMS_SQL.EXECUTE(v_cur);

    IF DBMS_SQL.FETCH_ROWS(v_cur) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_emp_id);
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
    END IF;

    -- Always close with IS_OPEN guard
    IF DBMS_SQL.IS_OPEN(v_cur) THEN
        DBMS_SQL.CLOSE_CURSOR(v_cur);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF v_cur IS NOT NULL AND DBMS_SQL.IS_OPEN(v_cur) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cur);
        END IF;
        RAISE;
END;
/

Cause 3: Sharing Cursors Across Sessions or Threads in Connection Pools

Cursors are session-scoped resources. In connection pool environments, if a cursor is declared at the package level and a pooled connection is reused by a different thread or session, the original cursor context no longer exists, resulting in ORA-01023.

-- BAD: Package-level cursor shared across sessions (dangerous)
/*
CREATE OR REPLACE PACKAGE risky_pkg AS
    CURSOR g_cursor IS SELECT * FROM employees; -- session-bound, pool-unsafe!
END risky_pkg;
*/

-- GOOD: Procedure-local cursor, always cleaned up
CREATE OR REPLACE PROCEDURE safe_emp_fetch(p_dept IN NUMBER) AS
    CURSOR l_cur IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept;
    v_id   employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN l_cur;
    LOOP
        FETCH l_cur INTO v_id, v_name;
        EXIT WHEN l_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
    END LOOP;
    CLOSE l_cur;
EXCEPTION
    WHEN OTHERS THEN
        IF l_cur%ISOPEN THEN CLOSE l_cur; END IF;
        RAISE;
END;
/

---

Quick Fix Solutions

• Always wrap cursor close logic inside EXCEPTION blocks using %ISOPEN or DBMS_SQL.IS_OPEN.
• Use cursor FOR loops whenever possible — Oracle handles OPEN, FETCH, and CLOSE automatically, eliminating ORA-01023 entirely.

-- Safest pattern: implicit cursor FOR loop
BEGIN
    FOR rec IN (SELECT employee_id, first_name FROM employees WHERE rownum <= 5) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' | ' || rec.first_name);
    END LOOP;
    -- Cursor is automatically closed. No ORA-01023 possible.
END;
/

---

Prevention Tips

1. Enforce OPEN → FETCH → CLOSE discipline — Make it a team code review checklist item. Every cursor open must have a corresponding close in both normal flow and exception handlers.
2. Prefer implicit cursors and FOR loops — They are not only safer but often perform comparably. Reserve explicit cursors and DBMS_SQL for cases where dynamic SQL or REF CURSORs are truly necessary.

---

Related Errors

Error Code	Description
ORA-01001	Invalid cursor — cursor handle itself is invalid
ORA-01002	Fetch out of sequence — FETCH after COMMIT on SELECT FOR UPDATE
ORA-06511	PL/SQL: cursor already open — re-opening an open cursor
ORA-01000	Maximum open cursors exceeded — too many unclosed cursors

---

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