PL/SQL Cursors - ORA-01000 from Unclosed Exceptions

PL/SQL Cursors Explained is a fundamental concept in Database development. In Oracle, a cursor is a pointer to a private memory area (Context Area) that stores the result of a SELECT statement. While SQL is a set-based language, real-world business logic often requires iterative processing where each record must be evaluated individually before an action is taken.

In this guide, we'll break down exactly what PL/SQL Cursors Explained is, why it was designed this way to bridge the gap between set-based SQL and procedural logic, and how to use it correctly in real projects. We will explore the memory mechanics behind cursors and how to leverage cursor attributes to write defensive, production-grade code.

By the end, you'll have both the conceptual understanding and practical code examples to use PL/SQL Cursors Explained with confidence.

Why PL/SQL Cursors Leak Handles and Crash Your App

A PL/SQL cursor is a database handle that points to the result set of a query and tracks its iteration state. In Java, every time you call a stored procedure or execute a query that returns a result set via JDBC, the database allocates a private SQL area — a cursor — to manage that execution. The core mechanic: each cursor consumes a slot in the database's fixed-size cursor cache (often set to 300 by default). When you open a cursor but fail to close it in your Java code, that slot remains occupied until the JDBC connection is closed or the cursor is explicitly freed. This is not a memory leak on the JVM heap — it's a database-side resource leak that manifests as ORA-01000: maximum open cursors exceeded.

In practice, every ResultSet, CallableStatement, and PreparedStatement in JDBC implicitly opens a cursor on the Oracle server. The key property: Oracle does not automatically close cursors when the Java object goes out of scope or is garbage collected — the cursor lives until the connection is closed or you call close() on the statement/result set. Connection pools exacerbate this: if you return a connection to the pool without closing all its cursors, those cursors remain open and accumulate across pool checkouts. A typical production system with 50 connections and a default cursor limit of 300 can exhaust the pool in as few as 6 unclosed cursors per connection.

You must treat every cursor open as a paired close() in a finally block or try-with-resources. This is non-negotiable in any Java application that calls PL/SQL — batch jobs, REST APIs, or ETL pipelines. The real-world impact: your app will run fine in staging with low concurrency, then crash in production under load with ORA-01000, taking down the entire service until connections are recycled or the database is restarted.

The Context Area: Implicit vs. Explicit Cursors

PL/SQL Cursors Explained is a core feature of PL/SQL. It was designed to solve a specific problem: SQL is naturally set-based, but procedural languages often need to manipulate individual rows. Cursors act as the bridge.

There are two primary types: 1. Implicit Cursors: Automatically created by Oracle whenever you execute a DML statement (INSERT, UPDATE, DELETE) or a SELECT INTO. You access their metadata using the SQL% prefix. 2. Explicit Cursors: Defined by the developer in the DECLARE section for queries that return multiple rows.

They exist to give you granular control over the context area, allowing you to track how many rows were affected (%ROWCOUNT), if a row was found (%FOUND), or if the cursor is still open (%ISOPEN). Managing these properly is the difference between a high-performance application and one that suffers from memory leaks.

-- io.thecodeforge: Standard Explicit Cursor Implementation
DECLARE
    -- 1. Declaration
    CURSOR c_forge_projects IS
        SELECT name, status FROM forge_projects WHERE active = 'Y';
    
    v_name   forge_projects.name%TYPE;
    v_status forge_projects.status%TYPE;
BEGIN
    -- 2. Opening the cursor
    OPEN c_forge_projects;
    
    LOOP
        -- 3. Fetching data into variables
        FETCH c_forge_projects INTO v_name, v_status;
        
        -- 4. Exit condition using cursor attributes
        EXIT WHEN c_forge_projects%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Project: ' || v_name || ' | Status: ' || v_status);
    END LOOP;
    
    -- 5. Closing the cursor to free memory
    CLOSE c_forge_projects;
END;

The Cursor FOR Loop: Modern Best Practices

When learning PL/SQL Cursors Explained, most developers hit the same set of gotchas. A critical mistake is forgetting to CLOSE an explicit cursor, leading to 'Maximum Open Cursors Exceeded' (ORA-01000) errors. Another is checking %NOTFOUND before the first FETCH, which yields unreliable results.

In modern PL/SQL, the Cursor FOR Loop is the gold standard. It implicitly handles the entire lifecycle: it opens the cursor, fetches rows into a record variable, and closes the cursor automatically even if an exception occurs. This 'managed' approach significantly reduces the surface area for bugs and resource leaks, though it still operates on a row-by-row basis (RBAR).

-- io.thecodeforge: The modern, cleaner Cursor FOR Loop approach
-- This is the production-grade way to handle multi-row results
DECLARE
    v_processed_count NUMBER := 0;
BEGIN
    -- Managed cursor: No need for explicit OPEN, FETCH, or CLOSE
    FOR r_project IN (SELECT name, status FROM forge_projects WHERE active = 'Y') LOOP
        DBMS_OUTPUT.PUT_LINE('Processing: ' || r_project.name);
        
        -- Complex business logic here
        v_processed_count := v_processed_count + 1;
    END LOOP;
    
    -- Accessing implicit cursor attribute for the last DML
    DBMS_OUTPUT.PUT_LINE('Total Processed: ' || v_processed_count);
END;

Cursor Attributes in Depth — %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

Cursor attributes are your only window into the state of the cursor. For implicit cursors, use the SQL% prefix. For explicit cursors, use cursor_name%. Here's what each does and when it's safe to call:

• %FOUND: Returns TRUE if the most recent FETCH returned a row. For implicit cursors, returns TRUE if the DML affected at least one row.
• %NOTFOUND: Opposite of %FOUND. Critical for loop exit conditions.
• %ISOPEN: Returns TRUE if the cursor is open. Check this before opening to avoid "cursor already open" error.
• %ROWCOUNT: Number of rows fetched so far. For implicit cursors, number of rows affected by the DML.

A common trap: calling %NOTFOUND immediately after OPEN but before any FETCH returns NULL, not TRUE. That's why you must FETCH before checking — except in a Cursor FOR Loop where Oracle does it for you.

-- io.thecodeforge: Demonstrating cursor attributes safely
DECLARE
    CURSOR c_emp IS SELECT employee_id FROM employees WHERE department_id = 50;
    v_eid employees.employee_id%TYPE;
BEGIN
    IF NOT c_emp%ISOPEN THEN
        OPEN c_emp;
    END IF;
    
    LOOP
        FETCH c_emp INTO v_eid;
        EXIT WHEN c_emp%NOTFOUND;  -- only safe after first FETCH
        DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_eid || ' (row ' || c_emp%ROWCOUNT || ')');
    END LOOP;
    
    CLOSE c_emp;
    
    -- Implicit cursor after DML
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100;
    DBMS_OUTPUT.PUT_LINE('Updated rows: ' || SQL%ROWCOUNT);
END;

Error Handling with Cursors — NO_DATA_FOUND and TOO_MANY_ROWS

Implicit cursors (SELECT INTO) raise exceptions when the query returns zero rows (NO_DATA_FOUND) or more than one row (TOO_MANY_ROWS). Explicit cursors handle these cases gracefully: no rows simply means no FETCH and %NOTFOUND becomes TRUE. The mismatch catches many developers off guard.

If you need to use SELECT INTO with a possibility of zero rows, wrap it in a BEGIN EXCEPTION block. Alternatively, use a cursor FOR loop that does nothing when no rows match — no exception, no special handling.

Explicit cursors with FOR UPDATE can raise deadlock (ORA-00060) if rows are locked by another session. Always use NOWAIT or WAIT n to control lock behaviour.

-- io.thecodeforge: Handling cursor-related exceptions
DECLARE
    CURSOR c_emp IS SELECT salary FROM employees WHERE employee_id = 99999;
    v_sal employees.salary%TYPE;
BEGIN
    -- Option 1: Use cursor for loop (no exception for no rows)
    FOR r IN c_emp LOOP
        DBMS_OUTPUT.PUT_LINE('Salary: ' || r.salary);
    END LOOP;
    
    -- Option 2: Select INTO with exception handling
    BEGIN
        SELECT salary INTO v_sal FROM employees WHERE employee_id = 99999;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_sal := 0;
        WHEN TOO_MANY_ROWS THEN
            v_sal := NULL;
    END;
    
    DBMS_OUTPUT.PUT_LINE('Salary after exception handling: ' || NVL(v_sal, -1));
END;

Bulk Collect with Cursors — When Row-by-Row Is Too Slow

RBAR (Row By Agonizing Row) is the enemy of performance. Every FETCH incurs a network round-trip between PL/SQL and SQL engine. For large result sets (thousands of rows), the overhead becomes unacceptable. BULK COLLECT fetches rows in batches, typically 100 at a time (or using LIMIT).

Use BULK COLLECT when you need to process a result set procedurally but cannot use set-based SQL. It reduces context switches dramatically. Combine it with FORALL for DML operations to further boost performance.

Trade-off: memory consumption. If you BULK COLLECT without a LIMIT clause, you might pull the entire result set into memory, causing ORA-04036 (PGA memory exhausted). Always use LIMIT.

-- io.thecodeforge: Using BULK COLLECT with LIMIT for safe batch processing
DECLARE
    CURSOR c_emp IS SELECT employee_id, salary FROM employees WHERE department_id = 50;
    TYPE t_emp_tab IS TABLE OF c_emp%ROWTYPE;
    v_emps t_emp_tab;
    v_limit CONSTANT POSITIVE := 100;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp BULK COLLECT INTO v_emps LIMIT v_limit;
        EXIT WHEN v_emps.COUNT = 0;
        
        FOR i IN 1..v_emps.COUNT LOOP
            -- Process each row individually
            DBMS_OUTPUT.PUT_LINE('Processing: ' || v_emps(i).employee_id);
        END LOOP;
    END LOOP;
    CLOSE c_emp;
END;

Cursor Variable (REF CURSOR) — The Only Way to Pass Result Sets Around

A normal explicit cursor is a fixed query, hardcoded at compile time. That's fine for simple stuff, but the second you need to pass a result set to another procedure, return a dynamic query from a function, or build a cursor based on runtime parameters, you're stuck. That's where REF CURSOR comes in. It's a pointer to a result set, not the query itself. You can pass it around like a hot potato. Weak REF CURSORs let you shape the query on the fly — strong ones enforce a fixed return structure at compile time. In production, you'll mostly see SYS_REFCURSOR as the return type of functions that build dynamic reports or filter logic by user roles. Why this matters: static cursors force you into copy-paste hell when you need the same loop logic against different tables or conditions. REF CURSOR gives you one loop, one handler, and a parameterized query. Saves you from the code rot that kills maintainability.

// io.thecodeforge — database tutorial

CREATE OR REPLACE FUNCTION get_orders_by_status(
    p_status IN VARCHAR2
) RETURN SYS_REFCURSOR
IS
    v_cursor SYS_REFCURSOR;
BEGIN
    -- Build the query dynamically — status isn't known until runtime
    OPEN v_cursor FOR
        'SELECT order_id, customer_name, order_date, total_amount
         FROM orders
         WHERE status = :status'
        USING p_status;

    -- Return the pointer, caller is responsible for fetching and closing
    RETURN v_cursor;
END get_orders_by_status;
/

-- Caller fetches the result set
DECLARE
    v_order_id     orders.order_id%TYPE;
    v_customer     orders.customer_name%TYPE;
    v_order_date   orders.order_date%TYPE;
    v_total        orders.total_amount%TYPE;
    v_cursor       SYS_REFCURSOR;
BEGIN
    v_cursor := get_orders_by_status('SHIPPED');

    LOOP
        FETCH v_cursor INTO v_order_id, v_customer, v_order_date, v_total;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_order_id || ' | ' || v_customer || ' | ' || v_total);
    END LOOP;

    CLOSE v_cursor;   -- You open it, you close it. No exceptions.
END;
/

Cursor FOR UPDATE — When You Need to Lock Rows and Modify Them Without Corruption

You're looping through orders, applying discounts, and updating totals. Without a lock, two sessions reading the same row will overwrite each other's work. That's a corrupt database with a side of angry customers. CURSOR FOR UPDATE solves this by acquiring row-level locks as you fetch. The database won't let anyone else modify those rows until you commit or roll back. The default wait behavior is infinite — that's a deadlock invitation. In production, you always set WAIT N (e.g., WAIT 5) so your process gives up after N seconds instead of hanging the whole app. Pair this with WHERE CURRENT OF to update the row without re-querying — you reference the cursor's current position directly, which is faster and safer than re-specifying the WHERE clause. Why this matters: row-by-row updates without locking are a race condition disaster. With FOR UPDATE and WHERE CURRENT OF, you get atomic read-modify-write. No lost updates. No phantom locks hanging around forever.

// io.thecodeforge — database tutorial

DECLARE
    -- Lock rows for 10 seconds max, raise error if lock can't be acquired
    CURSOR c_pending_orders IS
        SELECT order_id, total_amount
        FROM orders
        WHERE status = 'PENDING'
        FOR UPDATE OF status WAIT 10;
BEGIN
    FOR rec IN c_pending_orders LOOP
        -- Apply 5% loyalty discount
        UPDATE orders
        SET total_amount = rec.total_amount * 0.95,
            status = 'PROCESSED'
        WHERE CURRENT OF c_pending_orders;  -- Directly targets the locked row

        DBMS_OUTPUT.PUT_LINE('Order ' || rec.order_id || ' discounted to ' || (rec.total_amount * 0.95));
    END LOOP;

    COMMIT;  -- Release all locks atomically
END;
/

Explicit Cursor Lifecycle: Open, Fetch, Close — Or Else

Every explicit cursor you write goes through three stages: OPEN, FETCH, and CLOSE. Miss any one, and you leak memory in the PGA. Oracle allocates a private SQL area when you OPEN. If you never CLOSE, that handle stays allocated until the session dies. In a production OLTP system with hundreds of concurrent sessions, this is how you kill the database's shared pool.

Why do you need explicit cursors at all? When you need multiple result sets, or when you must fetch rows in batches with control over each step. But here's the rule: never OPEN without a corresponding CLOSE in a structured exception block. Use a pattern where you OPEN, loop FETCH into variables until %NOTFOUND, then CLOSE. Wrap the FETCH in an EXCEPTION section that CLOSEs on error. If you forget, your DBA will find you.

The HOW is simple. DECLARE the cursor with SELECT, OPEN it, FETCH into variables, check %FOUND or %NOTFOUND, CLOSE. That's it. Don't overthink it. Just respect the lifecycle.

// io.thecodeforge — database tutorial

DECLARE
   CURSOR emp_cur IS
      SELECT employee_id, last_name
      FROM employees
      WHERE department_id = 50;
   v_id   employees.employee_id%TYPE;
   v_name employees.last_name%TYPE;
BEGIN
   OPEN emp_cur;
   LOOP
      FETCH emp_cur INTO v_id, v_name;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name);
   END LOOP;
   CLOSE emp_cur;  -- mandatory!
EXCEPTION
   WHEN OTHERS THEN
      IF emp_cur%ISOPEN THEN
         CLOSE emp_cur;  -- close on error too
      END IF;
      RAISE;
END;

Cursor Parameters: Stop Hard-Coding Where Clauses

Hard-coding WHERE clauses in cursor definitions is bad practice. You create a new cursor for every variation: CURSOR emp_dept_10, CURSOR emp_dept_20, ad infinitum. That's copy-paste garbage. Instead, parameterize your cursor like a function. Declare parameters in parentheses after the cursor name, then reference them inside the SELECT.

Why? Because one parameterized cursor replaces ten hard-coded ones. You call it with different values at OPEN time. The execution plan is cached and reused for each bind value, saving parsing overhead. This is how you write maintainable code that doesn't make your code reviewer cry.

Syntax is clean: CURSOR cursor_name (param1 datatype, param2 datatype) IS SELECT ... WHERE column = param1. Then OPEN cursor_name(value1, value2). You can also use default values for optional parameters. Just remember: the datatype must match the column, not some arbitrary variable type. Use %TYPE when possible to stay in sync with the table definition.

// io.thecodeforge — database tutorial

DECLARE
   CURSOR emp_by_dept (p_dept_id employees.department_id%TYPE) IS
      SELECT employee_id, last_name
      FROM employees
      WHERE department_id = p_dept_id
      ORDER BY last_name;
   v_id   employees.employee_id%TYPE;
   v_name employees.last_name%TYPE;
BEGIN
   -- Same cursor, two departments
   OPEN emp_by_dept(50);
   LOOP
      FETCH emp_by_dept INTO v_id, v_name;
      EXIT WHEN emp_by_dept%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept 50: ' || v_name);
   END LOOP;
   CLOSE emp_by_dept;
   
   OPEN emp_by_dept(60);
   LOOP
      FETCH emp_by_dept INTO v_id, v_name;
      EXIT WHEN emp_by_dept%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept 60: ' || v_name);
   END LOOP;
   CLOSE emp_by_dept;
END;