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
Appendix entry
OPEN FOR
Related
EXECUTE IMMEDIATE BULK COLLECT LIMIT DBMS_SQL DBMS_ASSERT

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.

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.

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;
/

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.

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.

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.

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.

  • 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.

  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).
  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.