Database Cursors — When Unclosed Cursors Drain Your Pool

Most SQL you write is declarative — you describe the shape of the data you want, and the database engine figures out how to get it in one shot. That works beautifully for 95% of use cases. But sometimes you genuinely need to process rows one at a time, carry state between rows, or perform logic that depends on the result of the previous row before you can compute the next one. That's the moment cursors enter the room, and if you don't know how they actually work, they'll silently destroy your application's performance at scale.

Cursors exist to solve a fundamental mismatch: relational databases think in sets, but procedural code — and many real business problems — think in sequences. Consider calculating a running bank balance, applying tiered commission rules where each row's rate depends on the cumulative total so far, or processing a feed of CDC events in strict order. These problems resist pure set-based solutions. Cursors provide a controlled, stateful way to walk through a result set row by row while the database engine manages memory, locking, and position tracking behind the scenes.

By the end of this article you'll understand exactly what happens inside the database engine when you declare a cursor, how the different cursor types (static, keyset, dynamic, forward-only) affect locks, memory, and consistency, how to write production-safe cursor code in both T-SQL and PL/pgSQL, and — critically — how to recognize when a cursor is the wrong tool and what to replace it with. You'll also walk away knowing the three cursor mistakes that silently corrupt data or cripple query performance in prod.

What Is a Database Cursor?

A database cursor is a pointer to a result set that allows you to fetch rows sequentially. The database engine creates a working table (often in tempdb or a temporary segment) that holds the current position and, depending on the cursor type, may store the entire result set or just a keyset.

You can think of it as an iterator over a query result. But unlike a typical programming language iterator, the cursor lives on the database server side and its lifecycle is managed through explicit DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE commands.

Cursors come in four main flavors, each with different trade-offs between consistency, memory, and freshness:

• Static cursor: Takes a snapshot of the result set at open time. No updates from other sessions are visible. Entire result set is materialized into a temporary table. Safe and consistent, but expensive on large datasets.
• Keyset cursor: Materializes only the unique row identifiers (keys) and fetches each row on demand from the underlying tables. Sees updates to non-key columns but not inserts/deletes from other sessions. Cheaper than static when row size is large.
• Dynamic cursor: Sees all changes (inserts, updates, deletes) made by other sessions after the cursor is opened. No materialization — just a pointer to the actual rows. Most expensive in terms of lock contention and server overhead.
• Forward-only cursor: Can only move forward through the result set. Default in most databases. Does not support SCROLL or FETCH PRIOR.

-- PL/SQL block nested in package IO_THECODEFORGE.CURSOR_DEMO
DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees WHERE department_id = 50;
  v_emp_id   employees.employee_id%TYPE;
  v_salary   employees.salary%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_emp_id, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' earns ' || v_salary);
  END LOOP;
  CLOSE emp_cursor;
END;
/

Cursor Internals: What Happens When You OPEN a Cursor

When the database executes OPEN cursor_name, a series of operations happen that many developers never consider:

1. The SQL associated with the cursor is parsed and optimized again (unless it's already in the shared pool).
2. Depending on the cursor type:
3. - Static: The database performs a full table or index scan according to the query plan and writes the result rows into a temporary segment (e.g., Oracle's temp tablespace, PostgreSQL's temp_buffers).
4. - Keyset: Only the unique key columns of the result set are stored in a temporary structure. At fetch time, each row is looked up by its key from the actual table.
5. - Dynamic: No materialization at all. The cursor simply holds a bookmark pointing to the next row based on the current state of the underlying tables.
6. The database sets up a cursor context in the session memory, which includes the current position, the statement handle, and any locks acquired.

This initialization cost is typically in the range of 0.1–1 ms per cursor open on modern hardware, but it can spike to seconds if the query is complex or the temp tablespace is slow.

-- Simulate cursor open overhead with different types
SET TIMING ON;

-- Static cursor (materializes full result)
DECLARE
  CURSOR c1 IS SELECT * FROM large_table;
  r large_table%ROWTYPE;
BEGIN
  OPEN c1;
  FETCH c1 INTO r;
  CLOSE c1;
END;
/

-- Forward-only cursor (no temp table)
DECLARE
  CURSOR c2 IS SELECT /*+ PARALLEL */ * FROM large_table;
  r large_table%ROWTYPE;
BEGIN
  OPEN c2;
  FETCH c2 INTO r;
  CLOSE c2;
END;
/

Row-by-Row vs Bulk Fetch: The Hidden Performance Trap

The biggest performance killer in cursor-based code is the overhead of fetching one row at a time inside a loop, especially when each fetch triggers a round-trip between the application and the database. Even with server-side cursors, each FETCH NEXT incurs context switching and cursor maintenance overhead.

Bulk fetching reduces the number of round trips from \(N\) (one per row) to \(N / batch_size\). The recommended batch size is typically between 100 and 1000, depending on column width and network latency.

DECLARE
  TYPE emp_tab_type IS TABLE OF employees%ROWTYPE;
  emp_tab emp_tab_type;
  CURSOR emp_cur IS
    SELECT employee_id, salary FROM employees WHERE department_id = 50;
  k_batch CONSTANT PLS_INTEGER := 100;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT k_batch;
    FOR i IN 1..emp_tab.COUNT LOOP
      -- Process each row (stateful logic here)
      DBMS_OUTPUT.PUT_LINE(emp_tab(i).employee_id || ' => ' || emp_tab(i).salary);
    END LOOP;
    EXIT WHEN emp_tab.COUNT < k_batch;
  END LOOP;
  CLOSE emp_cur;
END;
/

When Cursors Hurt: The Three Anti-Patterns to Avoid

Most cursor overhead in production comes from using them where a set-based solution would do the job better. Here are three concrete anti-patterns that silently degrade performance:

Anti-pattern 1: Running aggregate computations inside a cursor loop. Example: iterating over orders to calculate a running total when a window function SUM(amount) OVER (ORDER BY order_date) would do it in one pass.

Anti-pattern 2: Updating the same table you are reading from with a cursor. This often leads to deadlocks or consistent-read violations. Prefer MERGE or multi-table UPDATE with subqueries.

Anti-pattern 3: Cursors inside triggers. Row-level triggers already operate row-by-row. Adding a cursor inside a trigger multiplies context switching and can cause mutating-table errors in Oracle.

The common root: developers fall back to cursor-based thinking because it resembles the procedural loops they know from traditional programming languages.

-- Anti-pattern 1: Cursor with running total (slow)
DECLARE
  CURSOR c1 IS SELECT order_id, amount FROM orders ORDER BY order_date;
  v_running NUMBER := 0;
  v_ord orders%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_ord;
    EXIT WHEN c1%NOTFOUND;
    v_running := v_running + v_ord.amount;
    DBMS_OUTPUT.PUT_LINE(v_ord.order_id || ' total: ' || v_running);
  END LOOP;
  CLOSE c1;
END;
/

-- Better: set-based with window function
SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Production-Safe Cursor Patterns in PL/SQL and PL/pgSQL

When a cursor is justified (e.g., row-by-row business logic with state that cannot be expressed in SQL), you need to follow production patterns that prevent leaks and performance degradation.

Pattern 1: Parameterized cursors Always pass filter values as bind variables, not string concatenation. This avoids SQL injection and allows cursor reuse.

Pattern 2: Limit batch size with BULK COLLECT ... LIMIT As discussed, this is non-optional for any cursor processing thousands of rows.

Pattern 3: Always close and deallocate In PL/SQL, use a dedicated CLOSE statement. In some databases (e.g., PostgreSQL), a cursor is automatically closed at end of transaction, but it's safer to CLOSE explicitly.

Pattern 4: Use FOR UPDATE cursor only when needed If you need to update the current row, declare a cursor with FOR UPDATE OF column to lock only the relevant row. Avoid FOR UPDATE on read-only cursors.

Pattern 5: Prefer implicit cursors when possible Implicit cursors (like SELECT INTO or FOR rec IN (query) LOOP) are managed automatically and often more efficient because they use bulk collect internally.

CREATE OR REPLACE PACKAGE BODY io_thecodeforge.cursor_safe AS

  PROCEDURE process_commissions(p_dept_id IN NUMBER) IS
    CURSOR emp_cur(p_dept NUMBER) IS
      SELECT employee_id, salary FROM employees WHERE department_id = p_dept
      FOR UPDATE OF commission_pct; -- only lock necessary column
    TYPE emp_tab_type IS TABLE OF emp_cur%ROWTYPE;
    emp_tab emp_tab_type;
    k_batch CONSTANT PLS_INTEGER := 500;
  BEGIN
    OPEN emp_cur(p_dept_id);
    LOOP
      FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT k_batch;
      FOR i IN 1..emp_tab.COUNT LOOP
        -- complex commission calculation based on cumulative thresholds
        UPDATE employees
        SET commission_pct = compute_commission(emp_tab(i).salary,
                                                  i * k_batch)
        WHERE CURRENT OF emp_cur;
      END LOOP;
      EXIT WHEN emp_tab.COUNT < k_batch;
    END LOOP;
    CLOSE emp_cur;
  END;

END;
/

Cursor Lifecycle & Resource Leaks: Why Your DBA Will Hunt You Down

You opened a cursor and forgot to close it. That's not a slip-up—it's a memory leak that'll crater your database. Every open cursor consumes a private SQL area in the shared pool. Leave enough of them dangling, and you'll starve other sessions of memory, trigger ORA-01000 errors, or force a hard parse storm when the database hits its CURSOR_SPACE_FOR_SESSION limit.

Here's the lifecycle: DECLARE, OPEN, FETCH (maybe loop), CLOSE. Forgetting any step—especially CLOSE—is the classic junior mistake. In production, always wrap your cursor operations in a block that guarantees cleanup. PL/SQL's implicit cursors handle this, but explicit ones? You're the janitor.

Why this matters in the real world: A batch job that opens 10,000 cursors across 50 threads without closing them will bring down a production Oracle instance in under 90 seconds. Your DBA's first call isn't your manager. It's you. Don't make that call happen.

// io.thecodeforge — database tutorial

DECLARE
    CURSOR cur_orders IS
        SELECT order_id, total FROM orders WHERE status = 'PENDING';
    rec_orders cur_orders%ROWTYPE;
BEGIN
    OPEN cur_orders;
    LOOP
        FETCH cur_orders INTO rec_orders;
        EXIT WHEN cur_orders%NOTFOUND;
        -- process order
        UPDATE orders SET status = 'PROCESSED' WHERE order_id = rec_orders.order_id;
    END LOOP;
    CLOSE cur_orders; -- MUST close, or you leak resources
EXCEPTION
    WHEN OTHERS THEN
        IF cur_orders%ISOPEN THEN
            CLOSE cur_orders; -- always close in exception handler
        END IF;
        RAISE;
END;

Cursor Variables (Ref Cursors): When Dynamic SQL Demands Portability

You need to switch which query a cursor executes at runtime. Maybe the user picks a report type, or you're passing a result set between procedures. That's where ref cursors—cursor variables—save your neck. They're not tied to a static SQL statement at compile time. You OPEN them with a string or a dynamic query.

Why you'd risk this: Static cursors are rigid. Your monthly sales report needs different filters depending on the region. Instead of writing five near-identical cursors, define one weakly-typed ref cursor, build the SQL string dynamically, and OPEN it. The cost? More complexity and zero compile-time checking. One typo in your WHERE clause and it blows up at runtime.

Production reality: Ref cursors are the backbone of ORM layers and report engines. They're also the #1 source of SQL injection if you concatenate user input. Never do that. Use bind variables even in dynamic SQL. Your database's shared pool—and your security team—will love you.

// io.thecodeforge — database tutorial

CREATE OR REPLACE FUNCTION get_region_sales(p_region VARCHAR2)
    RETURN SYS_REFCURSOR
IS
    cur_sales SYS_REFCURSOR;
    v_sql     VARCHAR2(200);
BEGIN
    -- Build dynamic SQL: bind variable prevents injection
    v_sql := 'SELECT sale_date, amount ' ||
             'FROM sales WHERE region = :1 ' ||
             'ORDER BY sale_date DESC';
    -- OPEN ref cursor with bind variable
    OPEN cur_sales FOR v_sql USING p_region;
    RETURN cur_sales; -- caller closes the cursor
END;
/

-- Caller fetches and must close
VARIABLE cur_ref REFCURSOR;
EXEC :cur_ref := get_region_sales('EMEA');
PRINT cur_ref;

Cursor-Based Pagination: Why OFFSET Is a Performance Liar

You wrote 'OFFSET 10000 LIMIT 20' expecting fast pagination on a web UI. Now your page loads in 6 seconds. Congratulations—you've just scanned 10,020 rows the database will never return. OFFSET is a liar. It reads and discards rows. For large datasets, that's catastrophic.

Cursor-based pagination—key-based or seek pagination—fixes this. You don't skip rows. You start after the last row from the previous page. Think 'WHERE id > last_seen_id LIMIT 20'. That uses the index directly, reads exactly 20 rows, and the query plan stays constant regardless of page number. Page 1,000 costs the same as page 1. No scan, no discard, no pain.

When to use it: Any infinite scroll, activity feed, or API endpoint returning sorted result sets. You need a unique, sortable column—usually the primary key or a composite index. Bulk fetchers love this because you're not fetching rows you'll throw away. Your app's p95 latency drops from seconds to milliseconds. Don't tell your product manager how easy it was.

// io.thecodeforge — database tutorial

-- Page 1: no cursor, first 20 transactions
SELECT transaction_id, user_id, amount, created_at
FROM transactions
WHERE created_at >= '2024-01-01'
ORDER BY created_at, transaction_id
FETCH NEXT 20 ROWS ONLY;

-- Page N: use last seen (created_at, transaction_id) from previous page
SELECT transaction_id, user_id, amount, created_at
FROM transactions
WHERE (created_at, transaction_id) > (:last_created_at, :last_transaction_id)
  AND created_at >= '2024-01-01'
ORDER BY created_at, transaction_id
FETCH NEXT 20 ROWS ONLY;
-- Uses composite index on (created_at, transaction_id) — instant

Forward-Only Cursors: The Fastest Way to Stream 10M Rows Without Blowing Up Memory

Most developers assume cursors are memory hogs. That's true of scrollable cursors, which buffer rows to support random access and backward scrolling. But the default in PostgreSQL, SQL Server, and Oracle's implicit cursors is forward-only. That means no buffering, no spooling, no materialization. The server streams rows directly to your client as fast as the network allows.

Why does this matter? Because forward-only is the difference between a query returning results in 200ms and one that takes 10 seconds just to fill a temp table. If you don't need to jump backward or count rows before reading them, don't pay for that luxury. Every database cursor API I've seen defaults to forward-only for a reason: it's the path of least resource contention.

In production, always check your cursor declaration. Adding SCROLL in Oracle or INSENSITIVE in SQL Server turns a firehose into a bucket brigade. You want the firehose. Query pagination with forward-only cursors keyset-based? That's a billion-row loop that never hits disk. The rule: forward-only by default, scrollable only when you can justify the cost with a real use case.

// io.thecodeforge — database tutorial

-- Forward-only cursor for keyset pagination
-- No SCROLL, no buffering. Just raw streaming.
DECLARE cur CURSOR FORWARD_ONLY FOR
    SELECT id, payload
    FROM events
    WHERE id > :last_seen_id  -- keyset, not offset
    ORDER BY id
    FETCH NEXT 1000 ROWS ONLY;

OPEN cur;
FETCH NEXT FROM cur INTO @id, @payload;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process one row, then stream the next
    EXEC sp_handle_event @id, @payload;
    FETCH NEXT FROM cur INTO @id, @payload;
END

CLOSE cur;
DEALLOCATE cur;

Remarks on Cursors: The Skeleton Key Hidden in Every DBA's Toolbox

Most devs treat remarks like throwaway comments. In the cursor world, remarks can save your production environment from a late-night pager. A cursor remark is metadata attached to the server-side cursor that tells the optimizer and the DBA exactly what your code intends to do. In Oracle, it's a REM statement inside the cursor declaration. In SQL Server, it's a label on the cursor variable. Sounds trivial until you realize it's the only way to trace a runaway cursor back to the exact query and session.

Here's the ugly truth: when your production database runs out of tempdb space at 3 AM, your DBA will see a thousand cursor declarations in sp_who2. They won't know which one is yours, which one is leaking, or which one is a forgotten open cursor from a long-dead connection. A remark — something as simple as '/ ACCT_BAL_CALC /' — turns a mystery into a grep target. It's the difference between a two-hour war room and a two-minute fix.

Use remarks to annotate high-concurrency cursors, especially those tied to batch jobs. Mark the cursor's purpose, the module, and the JIRA ticket. Your future self and your DBA will thank you when the next incident review rolls around.

// io.thecodeforge — database tutorial

-- Oracle: REM statement attaches to cursor metadata
DECLARE
    CURSOR c_invoice IS
        -- REM: Monthly batch billing (JIRA-4421)
        SELECT invoice_id, total FROM invoices
        WHERE status = 'PENDING'
        FOR UPDATE;
BEGIN
    OPEN c_invoice;
    LOOP
        FETCH c_invoice INTO v_id, v_total;
        EXIT WHEN c_invoice%NOTFOUND;
        UPDATE invoices SET status = 'PROCESSED'
        WHERE CURRENT OF c_invoice;
    END LOOP;
    CLOSE c_invoice;
END;

-- SQL Server: label via cursor_name
DECLARE monthly_bill_cursor CURSOR FOR
    SELECT invoice_id, total FROM invoices
    WHERE status = 'PENDING'
    ORDER BY due_date;

OPEN monthly_bill_cursor;
FETCH NEXT FROM monthly_bill_cursor INTO @id, @total;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE invoices SET status = 'PROCESSED'
    WHERE CURRENT OF monthly_bill_cursor;
    FETCH NEXT FROM monthly_bill_cursor INTO @id, @total;
END

CLOSE monthly_bill_cursor;
DEALLOCATE monthly_bill_cursor;

Forward-Only Cursors: The Fastest Path Through a Million Rows

A forward-only cursor is the most efficient cursor type for streaming large result sets because it forces the database to fetch rows sequentially without storing previous results. Unlike scrollable cursors that allow random access and require a temporary buffer, forward-only cursors read each row once and discard it after move. This eliminates memory overhead and avoids the hidden cost of snapshots. Use forward-only when you need to process all rows exactly once—exporting data, batch updates, or feeding a downstream system. The performance gain is linear: reading 10M rows with a forward-only cursor can be 5–10x faster than a scrollable cursor because the database never materializes the full result set. Many drivers default to forward-only; always check your client library settings to ensure you haven't accidentally enabled scrollability.

// io.thecodeforge — database tutorial

-- PostgreSQL: DECLARE with NO SCROLL forces forward-only
DECLARE cur_export CURSOR NO SCROLL FOR
  SELECT id, payload FROM large_table WHERE processed = FALSE;

MOVE FORWARD 1 FROM cur_export;
FETCH NEXT FROM cur_export INTO rec;

LOOP
  EXIT WHEN NOT FOUND;
  -- process rec
  FETCH NEXT FROM cur_export INTO rec;
END LOOP;

CLOSE cur_export;

Verification Cursors: Validate Before Mutation to Prevent Data Corruption

A verification cursor is a pre-check pattern that scans data before performing destructive updates. Instead of trusting an UPDATE or DELETE blindly, open a verification cursor first, evaluate each row against your business rules, log violations, and abort if anomalies exceed a threshold. This guards against silent data corruption caused by bad application state, broken joins, or stale caches. Use verification cursors in batch jobs, schema migrations, or data cleanup tasks where one wrong WHERE clause could corrupt millions of rows. The pattern is simple: open a cursor, FETCH a sample, validate invariants, then either proceed or roll back. This adds a small overhead—typically less than 5%—but prevents catastrophic failures that take days to reverse. Always close the verification cursor before starting the actual mutation to avoid holding locks across both scans.

// io.thecodeforge — database tutorial

DECLARE cur_verify CURSOR FOR
  SELECT id, account_balance FROM accounts WHERE status = 'active';

OPEN cur_verify;
FETCH NEXT FROM cur_verify INTO v_id, v_balance;

WHILE FOUND LOOP
  IF v_balance < 0 THEN
    RAISE EXCEPTION 'Negative balance found: %', v_id;
  END IF;
  FETCH NEXT FROM cur_verify INTO v_id, v_balance;
END LOOP;

CLOSE cur_verify;

-- Safe to proceed
UPDATE accounts SET status = 'frozen' WHERE status = 'active';

Summary: When to Use (and Not Use) Database Cursors

Database cursors are not inherently evil — they are a precise tool for row-by-row operations that batch processing cannot handle. Use cursors when you need to process each row with complex logic that depends on previous rows, when you must return a large result set incrementally to avoid memory pressure, or when dynamic SQL demands portable result-set handling via ref cursors. Avoid cursors when a single UPDATE, DELETE, or bulk collect with FORALL can accomplish the same work orders of magnitude faster. The hidden cost is context switching between the database engine and your procedural code — each FETCH round-trips across the server. Production-safe patterns always close cursors in exception handlers, use FOR UPDATE NOWAIT only when locking is required, and prefer forward-only cursors for streaming. The key takeaway: cursors are the scalpel, not the sledgehammer — use them for precision work, not bulk demolition.

// io.thecodeforge — database tutorial
-- When to choose a cursor vs. bulk operation
-- Use cursor: row-by-row dependency, streaming, dynamic SQL
-- Use bulk: set-based update, insert from select
-- Anti-pattern: cursor inside transaction that could be a single UPDATE

Summary: Cursor Lifecycle Rules That Prevent Leaks

Every cursor you open must be closed — no exceptions. Database cursors consume server-side memory, locks, and temporary storage. Resource leaks happen when exceptions are thrown between OPEN and CLOSE, or when developers forget that implicit cursors (like FOR loops) auto-close while explicit cursors do not. The production-safe pattern is: DECLARE cursor, OPEN, FETCH in loop with exception block that CLOSEs in both success and failure paths. Use SYS_REFCURSOR for dynamic SQL when you need to return result sets to client applications, but remember that these also must be closed by the consumer. Forward-only cursors with FAST_FETCH minimize memory but still require proper lifecycle management. Your DBA will hunt you down if cursors are left open — each open cursor consumes a database connection slot and can block DDL operations. Always wrap cursor logic in a dedicated procedure with EXCEPTION block that guarantees CLOSE via a nested block or a GOTO cleanup section.

// io.thecodeforge — database tutorial
DECLARE
  CURSOR c IS SELECT id FROM orders;
  v_id NUMBER;
BEGIN
  OPEN c;
  BEGIN
    LOOP
      FETCH c INTO v_id;
      EXIT WHEN c%NOTFOUND;
      -- process
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      CLOSE c; -- critical!
      RAISE;
  END;
  CLOSE c; -- normal path
END;