# Deep Dive — OPEN FOR

> A cursor variable: open it on any query — static or dynamic — fetch it in batches, hand it to a client, close it. The batched half of dynamic SQL.

Used in: [Chapter 6 — Dynamic SQL](../course-plan.md) · Appendix entry: [OPEN FOR](../appendix.md#open-for) · Related: [EXECUTE IMMEDIATE](execute-immediate.md), [BULK COLLECT](bulk-collect.md), [LIMIT](limit.md), [DBMS_SQL](dbms_sql.md), [DBMS_ASSERT](dbms_assert.md)

---

## 1. What It Is and When

`OPEN <ref_cursor> FOR <query>` associates a cursor variable (`REF CURSOR`) with a query. Unlike an explicit cursor, the query can be a string built at runtime, and the cursor variable can be passed around — returned from a function, handed to a client program, or converted to a `DBMS_SQL` cursor number.

Use it for:
- **Dynamic queries with large results** — `EXECUTE IMMEDIATE ... BULK COLLECT INTO` has no `LIMIT`; `OPEN FOR` + `FETCH ... BULK COLLECT ... LIMIT` does.
- **Returning result sets to clients** (JDBC, ODP.NET, Python) — a function returning `SYS_REFCURSOR`.
- **Dynamic ORDER BY / WHERE shapes** that cannot be expressed with binds.

## 2. Syntax

```sql
-- weak ref cursor: any query, checked at runtime
<cv> SYS_REFCURSOR;
-- strong ref cursor: fixed row shape, checked at compile time
TYPE <t> IS REF CURSOR RETURN <table>%ROWTYPE;
<cv> <t>;

OPEN <cv> FOR <select_statement>;                                  -- static
OPEN <cv> FOR <dynamic_string> [USING <bind> [, <bind>]...];      -- dynamic (weak cursors only)

FETCH <cv> INTO <var(s) | record>;
FETCH <cv> BULK COLLECT INTO <collection(s)> [LIMIT <n>];
CLOSE <cv>;

<cv>%FOUND   <cv>%NOTFOUND   <cv>%ROWCOUNT   <cv>%ISOPEN
```

- A ref cursor cannot drive a cursor `FOR` loop; you write the `OPEN`/`FETCH`/`CLOSE` yourself.
- `OPEN ... FOR` with a dynamic string requires a **weak** ref cursor (`SYS_REFCURSOR`).
- Binds are positional, same rules as [EXECUTE IMMEDIATE](execute-immediate.md).

## 3. Examples on the Course Schema

### 3a. Dynamic table, big result, flat memory

```sql
CREATE OR REPLACE PROCEDURE reprice_in (p_table VARCHAR2, p_status VARCHAR2) IS
  c      SYS_REFCURSOR;
  TYPE t_row  IS RECORD (order_id orders.order_id%TYPE, amount orders.amount%TYPE);
  TYPE t_rows IS TABLE OF t_row;
  l_rows t_rows;
  l_tab  VARCHAR2(128) := DBMS_ASSERT.SQL_OBJECT_NAME(p_table);
BEGIN
  OPEN c FOR 'SELECT order_id, amount FROM ' || l_tab || ' WHERE status = :1'
    USING p_status;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    FOR i IN 1 .. l_rows.COUNT LOOP
      l_rows(i).amount := pricing_pkg.reprice(l_rows(i).order_id, l_rows(i).amount);
    END LOOP;
    FORALL i IN 1 .. l_rows.COUNT
      EXECUTE IMMEDIATE 'UPDATE ' || l_tab || ' SET amount = :1 WHERE order_id = :2'
      USING l_rows(i).amount, l_rows(i).order_id;
    COMMIT;
  END LOOP;
  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    IF c%ISOPEN THEN CLOSE c; END IF;
    RAISE;
END;
/
```

### 3b. Returning a result set to a client

```sql
CREATE OR REPLACE FUNCTION customer_orders (p_customer_id NUMBER, p_from DATE)
  RETURN SYS_REFCURSOR
IS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR
    SELECT order_id, order_date, amount, status
    FROM   orders
    WHERE  customer_id = p_customer_id AND order_date >= p_from
    ORDER  BY order_date DESC;
  RETURN c;                 -- the client fetches and closes
END;
/
```

Static query, no string — `OPEN FOR` is not only for dynamic SQL. The client sets its own fetch size (JDBC `setFetchSize`, ODP `FetchSize`); 100–1,000 rows per round trip is the usual range.

### 3c. Strongly typed — compile-time checking

```sql
DECLARE
  TYPE t_order_cur IS REF CURSOR RETURN orders%ROWTYPE;
  c   t_order_cur;
  r   orders%ROWTYPE;
BEGIN
  OPEN c FOR SELECT * FROM orders WHERE customer_id = :id;    -- shape must match orders%ROWTYPE
  LOOP
    FETCH c INTO r;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(r.order_id);
  END LOOP;
  CLOSE c;
END;
```

A mismatch is `PLS-00382`/`ROWTYPE_MISMATCH` at compile time, not a runtime surprise. Strong cursors cannot be opened on a dynamic string.

### 3d. Dynamic ORDER BY — the safe way

```sql
CREATE OR REPLACE FUNCTION orders_sorted (p_customer_id NUMBER, p_sort_col VARCHAR2, p_dir VARCHAR2)
  RETURN SYS_REFCURSOR
IS
  c      SYS_REFCURSOR;
  l_col  VARCHAR2(128);
  l_dir  VARCHAR2(4);
BEGIN
  -- whitelist first, DBMS_ASSERT second
  IF UPPER(p_sort_col) NOT IN ('ORDER_DATE', 'AMOUNT', 'STATUS') THEN
    RAISE_APPLICATION_ERROR(-20001, 'invalid sort column');
  END IF;
  l_col := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_sort_col));
  l_dir := CASE WHEN UPPER(p_dir) = 'DESC' THEN 'DESC' ELSE 'ASC' END;

  OPEN c FOR 'SELECT order_id, order_date, amount, status FROM orders' ||
             ' WHERE customer_id = :1 ORDER BY ' || l_col || ' ' || l_dir
    USING p_customer_id;
  RETURN c;
END;
/
```

Three sort columns × two directions = six cursors in the shared pool, all bound on the value. Acceptable.

### 3e. Hand-off to DBMS_SQL when you need to describe columns

```sql
DECLARE
  c      SYS_REFCURSOR;
  n      INTEGER;
  l_cnt  INTEGER;
  l_desc DBMS_SQL.DESC_TAB;
BEGIN
  OPEN c FOR 'SELECT * FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(:tab);
  n := DBMS_SQL.TO_CURSOR_NUMBER(c);          -- c is no longer usable after this
  DBMS_SQL.DESCRIBE_COLUMNS(n, l_cnt, l_desc);
  FOR i IN 1 .. l_cnt LOOP
    DBMS_OUTPUT.PUT_LINE(l_desc(i).col_name);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(n);
END;
```

The reverse, `DBMS_SQL.TO_REFCURSOR`, lets you bind with `DBMS_SQL` (unknown number of binds) and then bulk-fetch with `OPEN FOR` semantics. See [DBMS_SQL](dbms_sql.md).

## 4. What Breaks at Scale

- **Not closing** — each open ref cursor is an open cursor in the session. A loop that opens one per iteration and never closes hits `ORA-01000: maximum open cursors exceeded` after `OPEN_CURSORS` iterations (often 300). Close in the exception handler too (3a).
- **Re-opening per iteration** — an `OPEN FOR` inside a loop is a parse (soft) per iteration. Open once outside the loop when the query does not change.
- **`FETCH ... INTO` one row at a time** on a ref cursor is the same row-by-row cost as any single-row fetch. Use `BULK COLLECT ... LIMIT`.
- **Returning a ref cursor over a huge result to a client** with a fetch size of 10 — the client makes a round trip per 10 rows. The cursor is not the problem; the client's fetch size is.

## 5. Pitfalls

1. `ORA-01000` — cursors opened and not closed, especially on the exception path.
2. `FOR r IN c LOOP` with a ref cursor — not allowed; explicit fetch loop only.
3. `EXIT WHEN c%NOTFOUND` immediately after `FETCH ... BULK COLLECT ... LIMIT` — the last partial batch is lost ([LIMIT](limit.md)).
4. A strong ref cursor opened on a dynamic string — compile error; use `SYS_REFCURSOR`.
5. Passing a ref cursor to a client and also fetching from it in PL/SQL — the position is shared; whoever fetches consumes rows.
6. `ORDER BY` built from user input without a whitelist — `DBMS_ASSERT` validates the *form* of an identifier, not whether the user should be sorting by it.
7. A ref cursor stored in a package variable across calls — it stays open across calls (and transactions); fine if intended, a leak if forgotten.

## 6. Related

- [EXECUTE IMMEDIATE](execute-immediate.md) — dynamic DML and single-row queries.
- [BULK COLLECT](bulk-collect.md) / [LIMIT](limit.md) — the fetch pattern.
- [DBMS_SQL](dbms_sql.md) — converting cursors when column or bind counts are unknown.
- [DBMS_ASSERT](dbms_assert.md) — identifiers in the string.