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.

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;

Frequently Asked Questions