# Deep Dive — DBMS_SQL

> The heavy tool: every step of a cursor's life as an explicit call. You need it when you do not know the columns or the binds until runtime — and almost never otherwise.

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

---

## 1. What It Is and When

`DBMS_SQL` exposes the cursor lifecycle — open, parse, bind, define, execute, fetch, read column values, close — as procedure calls. Native dynamic SQL ([EXECUTE IMMEDIATE](execute-immediate.md), [OPEN FOR](open-for.md)) does all of that in one statement, but it requires the **shape** of the statement to be known when you write the code: how many binds, how many columns, of what types.

`DBMS_SQL` is for the cases where that is not known:
- **Unknown columns** — "export any table to CSV", generic comparison tools, dynamic pivots.
- **Unknown binds** — a filter built from N optional criteria, each with its own bind.
- **Explicit cursor reuse** — parse once, bind and execute thousands of times without re-parsing.
- **Bridging** — convert to/from ref cursors (`TO_REFCURSOR`, `TO_CURSOR_NUMBER`), or return implicit result sets to a client (`RETURN_RESULT`, 12c+).

For everything else it is slower to write, slower to run (a PL/SQL call per column per row on fetch), and easier to leak.

## 2. Syntax — the lifecycle

```sql
c := DBMS_SQL.OPEN_CURSOR;                                   -- cursor number (INTEGER)
DBMS_SQL.PARSE(c, <statement>, DBMS_SQL.NATIVE);             -- statement: VARCHAR2 or CLOB
DBMS_SQL.BIND_VARIABLE(c, ':name', <value>);                 -- by NAME, any order; overloads per type
DBMS_SQL.BIND_ARRAY(c, ':name', <collection>);               -- bulk binds for DML
DBMS_SQL.DEFINE_COLUMN(c, <pos>, <var> [, <max_size>]);      -- for queries: declare output columns
DBMS_SQL.DEFINE_ARRAY(c, <pos>, <collection>, <cnt>, <lower>);  -- bulk fetch
n := DBMS_SQL.EXECUTE(c);                                    -- rows affected for DML; meaningless for SELECT
WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP                        -- one row (or one array batch)
  DBMS_SQL.COLUMN_VALUE(c, <pos>, <var>);                    -- read each column
END LOOP;
DBMS_SQL.VARIABLE_VALUE(c, ':name', <var>);                  -- OUT binds of a PL/SQL block
DBMS_SQL.CLOSE_CURSOR(c);

-- metadata
DBMS_SQL.DESCRIBE_COLUMNS(c, <col_cnt>, <desc_tab>);          -- DESC_TAB / DESC_TAB2 / DESC_TAB3 (longer names)
-- conversion
rc := DBMS_SQL.TO_REFCURSOR(c);          n := DBMS_SQL.TO_CURSOR_NUMBER(rc);
-- misc
DBMS_SQL.IS_OPEN(c)   DBMS_SQL.LAST_ROW_COUNT   DBMS_SQL.EXECUTE_AND_FETCH(c)
DBMS_SQL.RETURN_RESULT(rc)               -- 12c: implicit result set to the caller
```

## 3. Examples on the Course Schema

### 3a. Unknown columns — a generic CSV dump of any query

```sql
CREATE OR REPLACE PROCEDURE dump_csv (p_sql VARCHAR2) IS
  c        INTEGER := DBMS_SQL.OPEN_CURSOR;
  l_cnt    INTEGER;
  l_desc   DBMS_SQL.DESC_TAB;
  l_val    VARCHAR2(4000);
  l_line   VARCHAR2(32767);
  l_dummy  INTEGER;
BEGIN
  DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(c, l_cnt, l_desc);

  FOR i IN 1 .. l_cnt LOOP
    DBMS_SQL.DEFINE_COLUMN(c, i, l_val, 4000);      -- everything as VARCHAR2: implicit conversion
    l_line := l_line || CASE WHEN i > 1 THEN ',' END || l_desc(i).col_name;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(l_line);                     -- header

  l_dummy := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
    l_line := NULL;
    FOR i IN 1 .. l_cnt LOOP
      DBMS_SQL.COLUMN_VALUE(c, i, l_val);
      l_line := l_line || CASE WHEN i > 1 THEN ',' END || '"' || REPLACE(l_val, '"', '""') || '"';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(l_line);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN DBMS_SQL.CLOSE_CURSOR(c); END IF;
    RAISE;
END;
/
EXEC dump_csv('SELECT order_id, order_date, amount FROM orders WHERE customer_id = 42');
```

Note `DEFINE_COLUMN ... VARCHAR2` for every column: the one-type-fits-all trick that makes "unknown columns" workable. Dates and numbers convert with the session's NLS settings.

### 3b. Unknown binds — an optional-criteria search

```sql
CREATE OR REPLACE FUNCTION find_orders (p_customer_id NUMBER   DEFAULT NULL,
                                        p_status      VARCHAR2 DEFAULT NULL,
                                        p_min_amount  NUMBER   DEFAULT NULL)
  RETURN SYS_REFCURSOR
IS
  c     INTEGER := DBMS_SQL.OPEN_CURSOR;
  l_sql VARCHAR2(4000) := 'SELECT order_id, customer_id, amount, status FROM orders WHERE 1 = 1';
  l_n   INTEGER;
BEGIN
  IF p_customer_id IS NOT NULL THEN l_sql := l_sql || ' AND customer_id = :cust'; END IF;
  IF p_status      IS NOT NULL THEN l_sql := l_sql || ' AND status = :st';        END IF;
  IF p_min_amount  IS NOT NULL THEN l_sql := l_sql || ' AND amount >= :amt';      END IF;

  DBMS_SQL.PARSE(c, l_sql, DBMS_SQL.NATIVE);
  IF p_customer_id IS NOT NULL THEN DBMS_SQL.BIND_VARIABLE(c, ':cust', p_customer_id); END IF;
  IF p_status      IS NOT NULL THEN DBMS_SQL.BIND_VARIABLE(c, ':st',   p_status);      END IF;
  IF p_min_amount  IS NOT NULL THEN DBMS_SQL.BIND_VARIABLE(c, ':amt',  p_min_amount);  END IF;
  l_n := DBMS_SQL.EXECUTE(c);

  RETURN DBMS_SQL.TO_REFCURSOR(c);         -- caller fetches with ordinary FETCH ... BULK COLLECT
END;
/
```

With `EXECUTE IMMEDIATE`, binds are positional and all-or-nothing — you cannot bind "whichever of these three exist". `DBMS_SQL` binds by name, only the ones present. Eight combinations of criteria = eight cursors, all bound. (The alternative — `AND (:cust IS NULL OR customer_id = :cust)` with static SQL — is one cursor but a worse plan; choose per case.)

### 3c. Parse once, execute many — avoiding the per-iteration soft parse

```sql
DECLARE
  c   INTEGER := DBMS_SQL.OPEN_CURSOR;
  n   INTEGER;
BEGIN
  DBMS_SQL.PARSE(c, 'UPDATE orders SET status = :st WHERE order_id = :id', DBMS_SQL.NATIVE);
  FOR r IN (SELECT order_id, new_status FROM order_status_changes) LOOP
    DBMS_SQL.BIND_VARIABLE(c, ':st', r.new_status);
    DBMS_SQL.BIND_VARIABLE(c, ':id', r.order_id);
    n := DBMS_SQL.EXECUTE(c);                        -- no parse: the cursor is still open
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;
```

`EXECUTE IMMEDIATE` in the same loop soft-parses per iteration. This form does not. (And `FORALL` with `BIND_ARRAY` — next — beats both.)

### 3d. Bulk DML with BIND_ARRAY

```sql
DECLARE
  TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;     -- DBMS_SQL array binds need INDEX BY tables
  TYPE t_sts IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  l_ids t_ids; l_sts t_sts;
  c INTEGER := DBMS_SQL.OPEN_CURSOR;
  n INTEGER;
BEGIN
  FOR i IN 1 .. 10000 LOOP l_ids(i) := i; l_sts(i) := 'DONE'; END LOOP;
  DBMS_SQL.PARSE(c, 'UPDATE orders SET status = :st WHERE order_id = :id', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':id', l_ids);
  DBMS_SQL.BIND_ARRAY(c, ':st', l_sts);
  n := DBMS_SQL.EXECUTE(c);                              -- 10,000 rows, one round trip
  DBMS_SQL.CLOSE_CURSOR(c);
END;
```

This is what `FORALL` compiles to. Use `FORALL ... EXECUTE IMMEDIATE` unless the statement shape is dynamic beyond what binds can express.

### 3e. Bulk fetch with DEFINE_ARRAY

```sql
DECLARE
  TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  l_ids t_ids;
  c INTEGER := DBMS_SQL.OPEN_CURSOR;
  n INTEGER;
BEGIN
  DBMS_SQL.PARSE(c, 'SELECT order_id FROM orders WHERE status = :st', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(c, ':st', 'NEW');
  DBMS_SQL.DEFINE_ARRAY(c, 1, l_ids, 10000, 1);          -- batch of 10,000 into l_ids starting at 1
  n := DBMS_SQL.EXECUTE(c);
  LOOP
    n := DBMS_SQL.FETCH_ROWS(c);                         -- fetches up to 10,000
    DBMS_SQL.COLUMN_VALUE(c, 1, l_ids);                  -- whole batch into the collection
    -- process l_ids(1 .. n) ...
    l_ids.DELETE;
    EXIT WHEN n < 10000;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;
```

Or, simpler: `TO_REFCURSOR` after `EXECUTE` and use `FETCH ... BULK COLLECT ... LIMIT` ([OPEN FOR](open-for.md)).

### 3f. Implicit result sets to the client (12c+)

```sql
CREATE OR REPLACE PROCEDURE report_customer (p_id NUMBER) IS
  rc1 SYS_REFCURSOR; rc2 SYS_REFCURSOR;
BEGIN
  OPEN rc1 FOR SELECT * FROM customers WHERE customer_id = p_id;
  OPEN rc2 FOR SELECT * FROM orders WHERE customer_id = p_id ORDER BY order_date DESC;
  DBMS_SQL.RETURN_RESULT(rc1);
  DBMS_SQL.RETURN_RESULT(rc2);
END;
/
-- JDBC: stmt.execute("BEGIN report_customer(?); END;"); then getMoreResults() / getResultSet()
```

Two result sets from one call, no OUT parameters — the T-SQL style, for clients that expect it.

## 4. What Breaks at Scale

- **A PL/SQL call per column per row on fetch.** `COLUMN_VALUE` for 10 columns × 40M rows = 400M procedure calls. `DEFINE_ARRAY` / `TO_REFCURSOR` + `BULK COLLECT` cut that to per-batch. For large results, convert to a ref cursor as soon as the binding is done.
- **Leaks.** Every `OPEN_CURSOR` without `CLOSE_CURSOR` holds a session cursor. The exception path is where they leak. Always `IF DBMS_SQL.IS_OPEN(c) THEN CLOSE_CURSOR` in the handler.
- **Conversion to VARCHAR2 on the way out** (3a) is CPU per cell; fine for exports, wrong for hot paths.
- **Same injection rules.** The `PARSE` string is a string; identifiers in it go through [DBMS_ASSERT](dbms_assert.md), values through `BIND_VARIABLE`.

## 5. Pitfalls

1. Forgetting `CLOSE_CURSOR` on the exception path — `ORA-01000` eventually.
2. `ORA-01007: variable not in select list` — `DEFINE_COLUMN` position beyond the column count, or defined after a re-parse.
3. `ORA-01008: not all variables bound` — a placeholder in the text without a `BIND_VARIABLE` (names must match, including the colon).
4. Using the cursor number after `TO_REFCURSOR` (or the ref cursor after `TO_CURSOR_NUMBER`) — the source handle is invalidated: `ORA-29471`.
5. `EXECUTE` return value for a `SELECT` is meaningless; for DML it is the row count.
6. `DESC_TAB` truncates column names longer than 30 characters — use `DESC_TAB2`/`DESCRIBE_COLUMNS2` (or `DESC_TAB3`) for 12.2+ long identifiers.
7. Language flag `DBMS_SQL.V6`/`V7` on old code — use `NATIVE`.
8. Reaching for `DBMS_SQL` when the shape is known — three times the code of `EXECUTE IMMEDIATE`, for nothing.

## 6. Related

- [EXECUTE IMMEDIATE](execute-immediate.md) — the default for known shapes.
- [OPEN FOR](open-for.md) — ref cursors and bulk fetch; the conversion target.
- [DBMS_ASSERT](dbms_assert.md) — identifiers in the parsed string.
- [BULK COLLECT](bulk-collect.md) — after `TO_REFCURSOR`.