When you need dynamic SQL
Dynamic SQL executes SQL statements whose text is not known until runtime. Common use cases:
- DDL from PL/SQL (CREATE TABLE, ALTER, DROP)
- Queries with variable WHERE clause structures
- Generic frameworks operating on any table/column
- Multi-tenant applications with schema-per-tenant
EXECUTE IMMEDIATE: the simple approach
DECLARE
v_sql VARCHAR2(4000);
v_cnt NUMBER;
BEGIN
v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;
EXECUTE IMMEDIATE v_sql INTO v_cnt;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_cnt);
END;
Bind variables — always use them
The single most important rule in dynamic SQL: use bind variables for values, not string concatenation:
-- BAD (SQL injection risk, no cursor reuse):
v_sql := 'SELECT * FROM employees WHERE id = ' || p_id;
-- GOOD:
v_sql := 'SELECT * FROM employees WHERE id = :id';
EXECUTE IMMEDIATE v_sql INTO l_emp USING p_id;
Concatenating literal values into SQL statements creates SQL injection vulnerabilities and prevents cursor sharing in the shared pool.
DML with EXECUTE IMMEDIATE
EXECUTE IMMEDIATE
'UPDATE ' || v_table || ' SET status = :s WHERE id = :id'
USING v_new_status, v_record_id;
-- Check rows affected:
v_rows := SQL%ROWCOUNT;
RETURNING INTO clause
EXECUTE IMMEDIATE
'INSERT INTO orders VALUES (:1,:2,:3) RETURNING id INTO :4'
USING v_cust, v_date, v_amount
RETURNING INTO v_new_id;
DBMS_SQL for complex scenarios
DBMS_SQL handles cases EXECUTE IMMEDIATE can’t — dynamic column counts, bulk operations on dynamic queries, large CLOBs. It’s more verbose but more powerful:
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', p_dept_id);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_name, 100);
v_result := DBMS_SQL.EXECUTE(v_cursor);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_name);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
Security: validating object names
When using dynamic SQL with table or column names from user input, validate against the data dictionary:
SELECT COUNT(*) INTO v_valid FROM all_tables WHERE table_name = UPPER(p_table_name);
IF v_valid = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid table'); END IF;