Dynamic SQL

28.1 Native Dynamic SQL (EXECUTE IMMEDIATE)

DECLARE
    v_count NUMBER;
    v_table VARCHAR2(128) := 'EMPLOYEES';
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE tmp_log (id NUMBER, msg VARCHAR2(200))';   -- DDL

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_table) INTO v_count;

    EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * :pct WHERE department_id = :d'
        USING 1.05, 50;                                                          -- bind variables

    EXECUTE IMMEDIATE 'BEGIN raise_salary(:id, :pct, :out); END;' USING 100, 5, OUT v_count;
END;
/

28.2 Dynamic Queries with REF Cursors

Dynamic BULK COLLECT: EXECUTE IMMEDIATE sql BULK COLLECT INTO coll USING ....

OPEN rc FOR 'SELECT * FROM employees WHERE department_id = :d' USING p_dept;

28.3 DBMS_SQL

Use it when the number or types of columns or binds aren't known until runtime (method 4).

DBMS_SQL.TO_REFCURSOR / TO_CURSOR_NUMBER convert between the two styles. DESCRIBE_COLUMNS3.

28.4 Preventing SQL Injection

Always use bind variables for values.

Validate identifiers with DBMS_ASSERT: SIMPLE_SQL_NAME, SQL_OBJECT_NAME, ENQUOTE_NAME, ENQUOTE_LITERAL, SCHEMA_NAME.

Use invoker's rights carefully. Grant the least privilege.

SQL Firewall (23ai) adds defense in depth (Module 41).