# Deep Dive — EXECUTE IMMEDIATE

> One SQL text, many executions, values passed as binds. Concatenate a value into the string and you have written a new statement for every value — and an injection point.

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

---

## 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](dbms_assert.md)**. 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

```sql
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](open-for.md) 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

```sql
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

```sql
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

```sql
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

```sql
-- :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

```sql
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

```sql
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

```sql
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

```sql
-- 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

```sql
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](forall.md).

## 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](dbms_sql.md) 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](open-for.md).
- **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](open-for.md) — dynamic queries with big results.
- [DBMS_ASSERT](dbms_assert.md) — validating identifiers.
- [DBMS_SQL](dbms_sql.md) — when the number of columns or binds is unknown.
- [FORALL](forall.md) — bulk dynamic DML.
- [TRUNCATE](truncate.md) — the usual dynamic DDL, and its implicit commit.