Oracle ORA-06511 Error: Causes and Solutions Complete Guide

ORA-06511: PL/SQL: Cursor Already Open — Causes, Fixes & Prevention

ORA-06511 is thrown by Oracle when your PL/SQL code attempts to OPEN a cursor that is already in an open state. Oracle does not allow a single cursor to be opened twice simultaneously, so any attempt to do so without first closing it will immediately raise this error. It is one of the most common cursor-management mistakes in PL/SQL development, especially in large packages or procedures with complex retry logic.

---

Top 3 Causes

1. Opening a Cursor Without Checking %ISOPEN

The most frequent cause is calling OPEN cursor_name without verifying whether the cursor is already open. This commonly happens when a procedure is called multiple times within the same session or when the same cursor is referenced in multiple code paths.

-- BAD: No state check before OPEN
DECLARE
    CURSOR emp_cur IS
        SELECT employee_id, salary FROM employees;
BEGIN
    OPEN emp_cur;  -- First call is fine
    -- ... some logic ...
    OPEN emp_cur;  -- ORA-06511 raised here!
    CLOSE emp_cur;
END;
/

-- GOOD: Always check %ISOPEN before opening
DECLARE
    CURSOR emp_cur IS
        SELECT employee_id, salary FROM employees;
    v_id   employees.employee_id%TYPE;
    v_sal  employees.salary%TYPE;
BEGIN
    IF emp_cur%ISOPEN THEN
        CLOSE emp_cur;
    END IF;

    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO v_id, v_sal;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ' Salary: ' || v_sal);
    END LOOP;
    CLOSE emp_cur;
END;
/

---

2. Cursor Left Open After an Exception

When an exception is raised inside a PL/SQL block, execution jumps to the EXCEPTION section. If the cursor is not explicitly closed there, it remains open. The next time the same block or procedure runs, the cursor is still open — triggering ORA-06511.

-- BAD: Exception handler doesn't close the cursor
DECLARE
    CURSOR dept_cur IS SELECT department_id FROM departments;
    v_id departments.department_id%TYPE;
BEGIN
    OPEN dept_cur;
    FETCH dept_cur INTO v_id;
    RAISE_APPLICATION_ERROR(-20001, 'Simulated error');
    CLOSE dept_cur; -- Never reached!
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        -- Cursor is still open here — next call will fail!
END;
/

-- GOOD: Always close cursor in the EXCEPTION block
DECLARE
    CURSOR dept_cur IS SELECT department_id FROM departments;
    v_id departments.department_id%TYPE;
BEGIN
    OPEN dept_cur;
    FETCH dept_cur INTO v_id;
    RAISE_APPLICATION_ERROR(-20001, 'Simulated error');
    CLOSE dept_cur;
EXCEPTION
    WHEN OTHERS THEN
        IF dept_cur%ISOPEN THEN
            CLOSE dept_cur;  -- Safe cleanup
        END IF;
        DBMS_OUTPUT.PUT_LINE('Error handled: ' || SQLERRM);
        RAISE;
END;
/

---

3. Mixing Explicit OPEN with Cursor FOR LOOP

A cursor FOR LOOP automatically handles OPEN, FETCH, and CLOSE internally. If a developer manually opens a cursor before passing it to a FOR LOOP, or opens it again inside the loop body, a conflict arises and ORA-06511 is raised.

-- BAD: Manually opening a cursor before FOR LOOP
DECLARE
    CURSOR emp_cur IS SELECT employee_id FROM employees;
BEGIN
    OPEN emp_cur;         -- Manual open
    FOR rec IN emp_cur    -- FOR LOOP tries to open again -> ORA-06511!
    LOOP
        DBMS_OUTPUT.PUT_LINE(rec.employee_id);
    END LOOP;
END;
/

-- GOOD: Let FOR LOOP manage the cursor entirely
BEGIN
    FOR rec IN (
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE salary > 5000
        ORDER BY salary DESC
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || rec.employee_id ||
            ' | Name: ' || rec.first_name
        );
    END LOOP;
    -- No OPEN, FETCH, or CLOSE needed!
END;
/

---

Quick Fix Solutions

Situation	Fix
Cursor opened twice	Add IF cur%ISOPEN THEN CLOSE cur; END IF; before OPEN
Exception leaves cursor open	Add cursor close logic in every EXCEPTION block
Mixed FOR LOOP and manual OPEN	Remove the manual OPEN; let the FOR LOOP handle it
Package-level cursor leaking	Always reset cursor state at procedure entry

---

Prevention Tips

1. Default to Cursor FOR LOOPs
Whenever possible, use cursor FOR LOOP or inline query loops. Oracle handles all cursor lifecycle management automatically, making ORA-06511 impossible in that context. Reserve explicit cursors only for BULK COLLECT, REF CURSOR, or cases requiring dynamic SQL.

-- Preferred pattern: inline FOR LOOP (zero cursor management overhead)
BEGIN
    FOR r IN (SELECT employee_id, salary FROM employees WHERE rownum <= 10) LOOP
        DBMS_OUTPUT.PUT_LINE(r.employee_id || ': ' || r.salary);
    END LOOP;
END;
/

2. Enforce a Cursor Cleanup Checklist in Code Reviews
Add a mandatory rule to your team's coding standards: every PL/SQL block that uses an explicit cursor must include IF cursor%ISOPEN THEN CLOSE cursor; END IF; in its EXCEPTION section. Use static analysis tools such as PL/SQL Cop or Oracle SQL Developer's Code Analysis feature to automatically flag unclosed cursors during CI/CD pipelines.

---

Related Oracle Errors

• ORA-01001: invalid cursor — Opposite of ORA-06511; occurs when you FETCH or CLOSE a cursor that was never opened.
• ORA-01002: fetch out of sequence — Raised when FETCH is called after the cursor has already been closed or after the last row.
• ORA-04031: unable to allocate shared memory — Can be an indirect consequence of persistent cursor leaks exhausting Shared Pool memory over time.

---

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