Deep Dive — EXECUTE IMMEDIATE

1. What It Is and When

Native dynamic SQL: build a statement as a string at runtime and execute it. Needed when the structure of the statement is not known at compile time — a table name from a parameter, a column list built from metadata, DDL, a PL/SQL block assembled on the fly. Not needed when only the values vary: that is static SQL with variables.

The one rule: values are binds (USING), identifiers go through DBMS_ASSERT. A bound statement has one text and one cursor in the shared pool no matter how many values it sees; a concatenated one has one cursor per distinct value.

2. Syntax

EXECUTE IMMEDIATE <dynamic_string>
  [ INTO { <var> [, <var>]... | <record> } | BULK COLLECT INTO <collection> [, <collection>]... ]
  [ USING [IN | OUT | IN OUT] <bind_arg> [, [IN | OUT | IN OUT] <bind_arg>]... ]
  [ { RETURNING | RETURN } [BULK COLLECT] INTO <bind_arg> [, <bind_arg>]... ];

• <dynamic_string> — VARCHAR2 or CLOB (11g+); a single SQL statement without a trailing ;, or a PL/SQL block with its END;.
• Binds are matched to placeholders by position, not by name. :x used twice in the text needs two entries in USING.
• INTO is for single-row queries; BULK COLLECT INTO for multi-row (no LIMIT — use OPEN FOR for big results).
• OUT / IN OUT binds are for PL/SQL blocks; RETURNING INTO for DML with a RETURNING clause.
• DDL cannot take binds, and it commits implicitly.

3. Examples on the Course Schema

3a. DML with binds — one cursor for every value

CREATE OR REPLACE PROCEDURE close_old_orders (p_status VARCHAR2, p_before DATE) IS
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE orders SET status = ''CLOSED'' WHERE status = :1 AND order_date < :2'
    USING p_status, p_before;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' closed');
END;
/

(This one has no dynamic structure and should be static SQL. It is here to show the bind shape.)

3b. Dynamic table name — the actual use case

CREATE OR REPLACE PROCEDURE archive_to (p_table VARCHAR2, p_before DATE) IS
  l_table VARCHAR2(128) := DBMS_ASSERT.SQL_OBJECT_NAME(p_table);      -- must exist, or ORA-44002
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO ' || l_table || ' (order_id, customer_id, order_date, amount, status)' ||
    ' SELECT order_id, customer_id, order_date, amount, status FROM orders WHERE order_date < :1'
    USING p_before;
END;
/

3c. Single-row query — INTO

DECLARE
  l_cnt NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(:tab) || ' WHERE status = :1'
    INTO l_cnt
    USING 'NEW';
END;

3d. The same bind twice, and NULL binds

-- :1 appears twice → two USING entries
EXECUTE IMMEDIATE
  'UPDATE orders SET amount = amount * :1 WHERE amount * :1 < 1000 AND status = :2'
  USING p_factor, p_factor, p_status;

-- a NULL literal cannot be bound directly (PLS-00457); type it or use a variable
EXECUTE IMMEDIATE 'UPDATE orders SET status = :1 WHERE order_id = :2'
  USING TO_CHAR(NULL), p_order_id;

3e. RETURNING INTO

DECLARE
  l_new_amount orders.amount%TYPE;
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE orders SET amount = amount * 0.9 WHERE order_id = :1 RETURNING amount INTO :2'
    USING p_order_id
    RETURNING INTO l_new_amount;
END;

3f. A dynamic PL/SQL block with OUT binds

DECLARE
  l_pkg    VARCHAR2(128) := DBMS_ASSERT.SQL_OBJECT_NAME(p_pkg);   -- e.g. 'PRICING_PKG'
  l_result NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'BEGIN :out := ' || l_pkg || '.reprice(:id, :amt); END;'      -- note the trailing END;
    USING OUT l_result, IN p_order_id, IN p_amount;
END;

3g. DDL — no binds, implicit commit

EXECUTE IMMEDIATE 'ALTER TABLE orders TRUNCATE PARTITION ' ||
                  DBMS_ASSERT.SIMPLE_SQL_NAME(p_partition) || ' UPDATE INDEXES';
-- the current transaction is committed before the DDL runs

A partition name is an identifier, not an object — SIMPLE_SQL_NAME validates the form; SQL_OBJECT_NAME would fail because a partition is not a schema object.

3h. IN-lists — bind a collection, do not build the list

-- wrong: 'WHERE order_id IN (' || p_csv || ')'  → a new cursor per list, injection
-- right: a package-declared collection type bound as a table
DECLARE
  l_ids id_types.t_ids;     -- TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER, in a package spec
BEGIN
  -- fill l_ids ...
  EXECUTE IMMEDIATE
    'UPDATE orders SET status = ''HOLD'' WHERE order_id IN (SELECT COLUMN_VALUE FROM TABLE(:1))'
    USING l_ids;
END;

3i. Inside FORALL — bulk dynamic DML

FORALL i IN 1 .. l_ids.COUNT
  EXECUTE IMMEDIATE 'UPDATE ' || l_table || ' SET status = :1 WHERE order_id = :2'
  USING 'ARCHIVED', l_ids(i);

Parsed once, executed as array DML — see FORALL.

4. What Breaks at Scale

• Concatenated values = one cursor per distinct value. 100K customers → 100K child cursors of the "same" statement, each hard-parsed (CPU, library cache latches), each occupying shared pool. Symptoms: latch: shared pool, library cache: mutex X, ORA-04031. V$SQL shows thousands of near-identical texts.
• Soft parse per execution. Even bound, EXECUTE IMMEDIATE re-parses (soft) on every call; static SQL in the same loop keeps the cursor open and skips even that. In a hot loop of 500M iterations that is measurable. SESSION_CACHED_CURSORS softens it; DBMS_SQL with an explicitly kept cursor removes it; a static statement removes it best.
• BULK COLLECT INTO without LIMIT — all rows into PGA. Big dynamic results go through OPEN FOR.
• Privileges via roles do not apply inside definer's-rights PL/SQL. A dynamic statement against a table you can query from SQL*Plus (through a role) fails with ORA-00942/ORA-01031 from the procedure. Direct grants are needed.

5. Pitfalls

1. Values in the string — injection and cursor explosion. Every time.
2. ORA-01008: not all variables bound — fewer USING entries than placeholders (remember: repeated placeholder = repeated entry).
3. ORA-01006: bind variable does not exist — more USING entries than placeholders, or binds in DDL.
4. Trailing ; on a SQL statement → ORA-00911; missing END; on a PL/SQL block → ORA-06550.
5. Quoting: use q'[...]' literals for strings that contain quotes; four single quotes to embed one is unreadable.
6. PLS-00457 — binding a bare NULL. Type it (TO_NUMBER(NULL)) or use a variable.
7. ORA-00942 from a procedure for a table you "can see" — role-based privilege; needs a direct grant.
8. DDL in the middle of a transaction — commits it.
9. VARCHAR2(32767) overflow on a generated statement — use a CLOB string (11g+).
10. SQL%ROWCOUNT works after dynamic DML; SQL%FOUND too. SQL%BULK_ROWCOUNT works after FORALL ... EXECUTE IMMEDIATE.

6. Related

• OPEN FOR — dynamic queries with big results.
• DBMS_ASSERT — validating identifiers.
• DBMS_SQL — when the number of columns or binds is unknown.
• FORALL — bulk dynamic DML.
• TRUNCATE — the usual dynamic DDL, and its implicit commit.