ORA-06511: Cursor Already Open - Fix PL/SQL Cursor Errors

ORA-06511: PL/SQL: Cursor Already Open

Error Overview

Error Text: ORA-06511: PL/SQL: cursor already open

The ORA-06511 error is raised at runtime when PL/SQL code attempts to OPEN a cursor that is already in the open state. Oracle tracks the open/closed status of every explicit cursor, and issuing a second OPEN before the corresponding CLOSE is a programming logic error. This error is always thrown from PL/SQL and never from plain SQL.

Common Causes

1. Missing CLOSE Before Reopen

The most frequent cause is reopening a cursor inside a loop or conditional block without closing it first. If the OPEN statement is reached a second time — whether in the same block or after an exception handler returns control — Oracle raises ORA-06511.

2. Exception Paths That Skip the CLOSE

When an exception is raised inside a cursor-processing loop, the CLOSE statement in the normal execution path is bypassed. If the exception handler or a calling block then tries to reopen the same cursor, the cursor is still open from the previous attempt.

3. Cursor Declared at Package Level

Package-level cursors persist across calls to the package’s subprograms within the same session. If one procedure opens a package cursor and a second call to any procedure in the package tries to open it again before the session closes it, ORA-06511 is raised.

4. Forgetting %ISOPEN Before OPEN

Code that conditionally opens a cursor but omits the %ISOPEN guard will fail on any code path where the cursor is already open — for example, after an error recovery or when the same procedure is called re-entrantly.

5. Cursor FOR Loop Mixed With Explicit OPEN

Mixing cursor FOR loop syntax (which implicitly opens and closes the cursor) with explicit OPEN/CLOSE statements in the same block creates ambiguity and can result in double-open situations when code paths intersect.

Diagnostic Queries

Identify the Failing Program Unit

-- Find the PL/SQL object and line raising ORA-06511
SELECT
  e.owner,
  e.name,
  e.type,
  e.line,
  e.text
FROM dba_errors e
WHERE e.attribute = 'ERROR'
  AND e.text LIKE '%cursor%'
ORDER BY e.owner, e.name, e.line;

-- Check recent PL/SQL errors in the trace
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.module,
  s.action,
  s.sql_id,
  s.last_call_et
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

Find Open Cursors in the Current Session

-- Count open cursors per session (spot runaway cursor leaks)
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  COUNT(oc.cursor_type) AS open_cursors
FROM v$session s
JOIN v$open_cursor oc ON s.saddr = oc.saddr
WHERE s.username IS NOT NULL
GROUP BY s.sid, s.serial#, s.username, s.program
ORDER BY open_cursors DESC;

-- Show individual open cursors for a specific session
SELECT
  oc.sid,
  oc.cursor_type,
  oc.sql_text,
  oc.last_sql_active_time
FROM v$open_cursor oc
WHERE oc.sid = :target_sid
ORDER BY oc.last_sql_active_time DESC;

Review Package State for Cursor Leaks

-- Identify package-level cursors that may be left open across calls
SELECT
  po.owner,
  po.object_name,
  po.object_type,
  po.status,
  po.last_ddl_time
FROM dba_objects po
WHERE po.object_type IN ('PACKAGE', 'PACKAGE BODY')
  AND po.status = 'VALID'
ORDER BY po.owner, po.object_name;

-- Review cursor declarations in a specific package source
SELECT
  line,
  text
FROM dba_source
WHERE owner = :pkg_owner
  AND name  = :pkg_name
  AND type  = 'PACKAGE BODY'
  AND UPPER(text) LIKE '%CURSOR%'
ORDER BY line;

Step-by-Step Resolution

1. Guard Every OPEN With a %ISOPEN Check

The safest immediate fix is to check %ISOPEN before every explicit OPEN call.

DECLARE
  CURSOR c_employees IS
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = 10;
  r_emp c_employees%ROWTYPE;
BEGIN
  -- Safe open: only open if not already open
  IF NOT c_employees%ISOPEN THEN
    OPEN c_employees;
  END IF;

  LOOP
    FETCH c_employees INTO r_emp;
    EXIT WHEN c_employees%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(r_emp.last_name || ' - ' || r_emp.salary);
  END LOOP;

  -- Always close explicitly
  IF c_employees%ISOPEN THEN
    CLOSE c_employees;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    -- Ensure cursor is closed on any error path
    IF c_employees%ISOPEN THEN
      CLOSE c_employees;
    END IF;
    RAISE;
END;
/

2. Use Cursor FOR Loops to Eliminate Manual Lifecycle Management

Cursor FOR loops implicitly OPEN, FETCH, and CLOSE the cursor. They are the preferred pattern for simple iteration and completely eliminate ORA-06511.

-- Preferred: cursor FOR loop handles open/close automatically
BEGIN
  FOR r_emp IN (
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = 10
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.last_name || ' - ' || r_emp.salary);
  END LOOP;
  -- No OPEN or CLOSE needed; cursor is always properly closed
END;
/

-- Named cursor version (still safe, still auto-closed)
DECLARE
  CURSOR c_employees IS
    SELECT employee_id, last_name, salary
    FROM employees
    WHERE department_id = 10;
BEGIN
  FOR r_emp IN c_employees LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.last_name || ' - ' || r_emp.salary);
  END LOOP;
END;
/

3. Close Cursors in Exception Handlers

Add cursor cleanup to exception handlers to prevent stale open cursors from causing ORA-06511 on subsequent calls.

DECLARE
  CURSOR c_orders IS
    SELECT order_id, status, total_amount
    FROM orders
    WHERE status = 'PENDING';
  r_order c_orders%ROWTYPE;
BEGIN
  OPEN c_orders;
  LOOP
    FETCH c_orders INTO r_order;
    EXIT WHEN c_orders%NOTFOUND;

    -- Processing that may raise an exception
    process_order(r_order.order_id);
  END LOOP;
  CLOSE c_orders;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_orders%ISOPEN THEN CLOSE c_orders; END IF;
    DBMS_OUTPUT.PUT_LINE('No pending orders found.');
  WHEN OTHERS THEN
    IF c_orders%ISOPEN THEN CLOSE c_orders; END IF;
    RAISE;
END;
/

4. Fix Package-Level Cursors

For package-level cursors that persist across procedure calls, always close the cursor at the end of the procedure that opens it, and provide a cleanup procedure.

CREATE OR REPLACE PACKAGE employee_pkg AS
  PROCEDURE process_department(p_dept_id NUMBER);
  PROCEDURE cleanup;
END employee_pkg;
/

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
  -- Package-level cursor; persists for the session
  CURSOR c_emp (p_dept NUMBER) IS
    SELECT employee_id, last_name
    FROM employees
    WHERE department_id = p_dept;

  PROCEDURE process_department(p_dept_id NUMBER) AS
    r_emp c_emp%ROWTYPE;
  BEGIN
    -- Guard against double-open across calls
    IF c_emp%ISOPEN THEN
      CLOSE c_emp;
    END IF;

    OPEN c_emp(p_dept_id);
    LOOP
      FETCH c_emp INTO r_emp;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(r_emp.last_name);
    END LOOP;
    CLOSE c_emp;
  EXCEPTION
    WHEN OTHERS THEN
      IF c_emp%ISOPEN THEN CLOSE c_emp; END IF;
      RAISE;
  END process_department;

  PROCEDURE cleanup AS
  BEGIN
    IF c_emp%ISOPEN THEN CLOSE c_emp; END IF;
  END cleanup;
END employee_pkg;
/

Prevention Strategies

1. Prefer Implicit Cursors and Cursor FOR Loops

Implicit cursors (SELECT INTO, cursor FOR loops) manage their own lifecycle and cannot cause ORA-06511. Use them as the default pattern and reserve explicit cursors for cases where BULK COLLECT or FETCH LIMIT is required.

2. Standardize Exception Handling Templates

Establish a team coding standard that every explicit cursor block includes a matching CLOSE in both the normal path and all exception handlers. Code reviews should flag any OPEN without a corresponding guarded CLOSE.

3. Lint With Static Analysis

Use PL/SQL static analysis tools (Oracle SQLcl, Trivadis PL/SQL Cop, or custom scripts against DBA_SOURCE) to detect OPEN statements not paired with CLOSE or not guarded by %ISOPEN checks.

-- Simple pattern: find OPEN without nearby CLOSE in same source object
SELECT
  owner, name, type, line, text
FROM dba_source
WHERE REGEXP_LIKE(text, '\bOPEN\b', 'i')
  AND owner = 'YOUR_SCHEMA'
ORDER BY owner, name, line;

4. Monitor Open Cursor Counts

Set up a scheduled job to alert when any session accumulates an abnormally high open cursor count, which indicates cursor leaks that may eventually trigger ORA-06511 or ORA-01000.

-- Alert when a session exceeds 500 open cursors
SELECT
  s.sid,
  s.serial#,
  s.username,
  COUNT(*) AS open_cursor_count
FROM v$session s
JOIN v$open_cursor oc ON s.saddr = oc.saddr
WHERE s.username IS NOT NULL
GROUP BY s.sid, s.serial#, s.username
HAVING COUNT(*) > 500
ORDER BY open_cursor_count DESC;

Diagnostic Scripts

These Oracle Day by Day scripts can help diagnose cursor-related issues:

gvsess.sql — Identify active sessions with high cursor usage

ORA-06502 - PL/SQL numeric or value error
ORA-06508 - PL/SQL could not find program unit being called
ORA-06512 - At line (PL/SQL error stack traceback)
ORA-06530 - Reference to uninitialized composite
ORA-06532 - Subscript outside of limit
ORA-06533 - Subscript beyond count
ORA-01000 - Maximum open cursors exceeded

Emergency Response

Quick Fixes

Close all open cursors for a session by bouncing the package state

-- Force package re-initialization for the affected session
-- (run from the affected session itself)
DBMS_SESSION.RESET_PACKAGE;

Kill a session leaking cursors

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

Temporarily raise the open_cursors limit to buy time

ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH;

Post-Resolution Cleanup

-- Verify cursor counts are back to normal
SELECT
  s.username,
  MAX(oc_count.cnt) AS max_open_cursors
FROM v$session s
JOIN (
  SELECT saddr, COUNT(*) AS cnt
  FROM v$open_cursor
  GROUP BY saddr
) oc_count ON s.saddr = oc_count.saddr
WHERE s.username IS NOT NULL
GROUP BY s.username
ORDER BY max_open_cursors DESC;

-- Recompile any invalid package bodies after fixes
BEGIN
  DBMS_UTILITY.COMPILE_SCHEMA(schema => 'YOUR_SCHEMA', compile_all => FALSE);
END;
/