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

DBMS_SQL exposes the cursor lifecycle — open, parse, bind, define, execute, fetch, read column values, close — as procedure calls. Native dynamic SQL (EXECUTE IMMEDIATE, OPEN FOR) 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.

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

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.

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

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

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.

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

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.

  • 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, values through BIND_VARIABLE.

  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.