# Deep Dive — DBMS_ASSERT

> You cannot bind a table name. So validate it — and let the validation throw before the string ever reaches the parser.

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

---

## 1. What It Is and When

Binds cover values. Identifiers — table, column, schema, partition, package names — must be spliced into the SQL text, and that is where injection lives. `DBMS_ASSERT` (10gR2+) is the set of validators for that splice: each function either returns the identifier (possibly normalized) or raises an `ORA-44xxx` error.

Use one of them on **every** identifier that comes from outside the code — a parameter, a table row, a config value — before it goes into a dynamic statement. Values still go through binds; `DBMS_ASSERT` does not replace that.

## 2. Syntax

| Function | Checks | Returns | Error |
| --- | --- | --- | --- |
| `SQL_OBJECT_NAME(str)` | the object **exists** and is accessible to the caller (tables, views, packages, synonyms…) | the name as given | `ORA-44002` invalid object name |
| `SIMPLE_SQL_NAME(str)` | the string is a **valid simple identifier**: unquoted (letters, digits, `_ $ #`, starts with a letter) or double-quoted (anything but `"`) | the name as given | `ORA-44003` invalid SQL name |
| `QUALIFIED_SQL_NAME(str)` | a valid, possibly dotted, possibly db-linked name: `schema.obj@link` | the name as given | `ORA-44004` invalid qualified SQL name |
| `SCHEMA_NAME(str)` | the schema **exists** (exact case) | the name | `ORA-44001` invalid schema |
| `ENQUOTE_NAME(str [, capitalize])` | wraps in `"…"`, verifies no embedded `"`; uppercases unless `capitalize => FALSE` | quoted name | `ORA-44003` |
| `ENQUOTE_LITERAL(str)` | wraps in `'…'`, doubling embedded quotes | quoted literal | `ORA-44003` if already badly quoted |
| `NOOP(str)` | nothing — marks a string as deliberately unvalidated | the string | — |

`SQL_OBJECT_NAME` and `SCHEMA_NAME` hit the data dictionary; the others are pure string checks.

## 3. Examples on the Course Schema

### 3a. A table that must exist

```sql
CREATE OR REPLACE PROCEDURE purge_stage (p_table VARCHAR2) IS
  l_table VARCHAR2(128);
BEGIN
  l_table := DBMS_ASSERT.SQL_OBJECT_NAME(p_table);          -- ORA-44002 if not a real object
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_table;
END;
/
EXEC purge_stage('orders_stage');                            -- OK
EXEC purge_stage('orders_stage; DROP TABLE orders');         -- ORA-44002, nothing executed
```

`SQL_OBJECT_NAME` resolves the name the way SQL would for the current user: synonyms, case folding (unquoted → uppercase), and privileges. A table you cannot see is "invalid".

### 3b. A column name — valid form + whitelist

```sql
CREATE OR REPLACE FUNCTION top_orders (p_by VARCHAR2) RETURN SYS_REFCURSOR IS
  c     SYS_REFCURSOR;
  l_col VARCHAR2(128);
BEGIN
  IF UPPER(p_by) NOT IN ('AMOUNT', 'ORDER_DATE') THEN
    RAISE_APPLICATION_ERROR(-20001, 'sort by AMOUNT or ORDER_DATE');
  END IF;
  l_col := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_by));
  OPEN c FOR 'SELECT order_id FROM orders ORDER BY ' || l_col || ' DESC FETCH FIRST 10 ROWS ONLY';
  RETURN c;
END;
/
```

`SIMPLE_SQL_NAME` guarantees the string *is an identifier* — it cannot contain `;`, spaces or operators unless double-quoted. The whitelist guarantees it is one *you allow*. Both.

### 3c. A partition name — not an object

```sql
EXECUTE IMMEDIATE 'ALTER TABLE orders TRUNCATE PARTITION ' ||
                  DBMS_ASSERT.SIMPLE_SQL_NAME(p_partition) || ' UPDATE INDEXES';
```

Partitions, constraints, index partitions and columns are not schema objects; `SQL_OBJECT_NAME` would reject them. `SIMPLE_SQL_NAME` is the right check.

### 3d. Schema-qualified names

```sql
l_obj := DBMS_ASSERT.QUALIFIED_SQL_NAME(p_schema || '.' || p_table);   -- form only: 'APP.ORDERS'
l_obj := DBMS_ASSERT.SQL_OBJECT_NAME(p_schema || '.' || p_table);      -- form AND existence
```

### 3e. Preserving case — ENQUOTE_NAME

A table that was created with a quoted, mixed-case name:

```sql
l_table := DBMS_ASSERT.ENQUOTE_NAME(p_table, capitalize => FALSE);   -- "OrdersStage"
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || l_table INTO l_cnt;
```

With the default `capitalize => TRUE`, `'OrdersStage'` becomes `"ORDERSSTAGE"` — which is a *different* object. Decide which you mean.

### 3f. ENQUOTE_LITERAL — for the one place binds cannot go

DDL takes no binds. A column default from a parameter:

```sql
EXECUTE IMMEDIATE 'ALTER TABLE orders MODIFY status DEFAULT ' ||
                  DBMS_ASSERT.ENQUOTE_LITERAL(p_default_status);
-- p_default_status = O'NEW  →  'O''NEW'   (safe)
```

For DML and queries, `ENQUOTE_LITERAL` is the wrong tool: a literal is still a new cursor per value. Bind.

### 3g. What an injection attempt looks like against each function

```sql
DECLARE
  v VARCHAR2(4000) := 'orders WHERE 1=1; DELETE FROM customers';
BEGIN
  v := DBMS_ASSERT.SIMPLE_SQL_NAME(v);      -- ORA-44003 (space, semicolon)
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END;
/
DECLARE
  v VARCHAR2(4000) := '"orders WHERE 1=1; DELETE FROM customers"';
BEGIN
  v := DBMS_ASSERT.SIMPLE_SQL_NAME(v);      -- passes! it is a syntactically valid quoted identifier
  v := DBMS_ASSERT.SQL_OBJECT_NAME(v);      -- ORA-44002: no such object
END;
/
```

A double-quoted string can contain almost anything and still be a "valid name". When the identifier should be a real object, use `SQL_OBJECT_NAME`; when it should be one of a known set, whitelist.

## 4. What Breaks at Scale

- Nothing about `DBMS_ASSERT` is slow: the string checks are microseconds; `SQL_OBJECT_NAME` is a dictionary lookup (cached). Call it once per statement build, not per row.
- The scale issue is the one it prevents: unvalidated identifiers usually come with unbound values, and unbound values are the cursor explosion of [EXECUTE IMMEDIATE](execute-immediate.md) §4.

## 5. Pitfalls

1. Using `DBMS_ASSERT` on values — it does not make them safe or bindable. Values → binds.
2. `SIMPLE_SQL_NAME` alone as authorization — it validates form, not permission. Whitelist.
3. `ENQUOTE_NAME` uppercasing a mixed-case object name — wrong object, or `ORA-00942`.
4. `SQL_OBJECT_NAME` in an invoker's-rights procedure resolves against the *invoker's* schema and privileges; in definer's rights, the *definer's*. Role-granted privileges are invisible in definer's rights, so an object the user can see may be "invalid" for the procedure.
5. `SQL_OBJECT_NAME` on a partition, column or constraint name — `ORA-44002`; those are not objects.
6. Assuming the returned string is normalized — `SQL_OBJECT_NAME` and `SIMPLE_SQL_NAME` return exactly what you passed (a quoted name keeps its quotes).
7. Skipping validation "because the parameter comes from our own table" — the table's contents came from somewhere too.

## 6. Related

- [EXECUTE IMMEDIATE](execute-immediate.md) / [OPEN FOR](open-for.md) — where the validated name is used.
- [DBMS_SQL](dbms_sql.md) — same rules for its `PARSE` strings.
- [TRUNCATE](truncate.md) — the most common dynamic DDL.