Deep Dive — DBMS_ASSERT

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

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

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

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

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

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:

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:

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

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 §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 / OPEN FOR — where the validated name is used.
• DBMS_SQL — same rules for its PARSE strings.
• TRUNCATE — the most common dynamic DDL.