1. Why Dynamic SQL?

Standard PL/SQL uses Static SQL. The compiler parses the SQL statements at compile-time, verifies the tables and columns exist, and bakes the execution plan into the package.

Dynamic SQL is built as a string (VARCHAR2 or CLOB) at runtime and executed on the fly. You need Dynamic SQL for three primary reasons:

1. Executing DDL: You cannot run TRUNCATE TABLE or DROP INDEX in static PL/SQL.
2. Dynamic Objects: The table name or column names are passed as parameters (e.g., querying a dynamically generated staging table).
3. Dynamic Where Clauses: Building highly flexible search screens where the WHERE clause changes drastically based on user input.

2. Native Dynamic SQL (EXECUTE IMMEDIATE)

Introduced in Oracle 8i, Native Dynamic SQL (NDS) made dynamic execution incredibly simple.

Basic Execution (DDL)

PROCEDURE truncate_staging_table(p_table_name IN VARCHAR2) IS
BEGIN
    -- DDL must be executed dynamically
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;
END;

Using Bind Variables (Crucial for Security & Performance)

If you concatenate values directly into the SQL string, you risk SQL Injection and flood the Shared Pool with hard parses. Always use the USING clause.

DECLARE
    v_sql    VARCHAR2(1000);
    v_dept   NUMBER := 10;
    v_salary NUMBER;
BEGIN
    -- The :b_dept is a bind placeholder
    v_sql := 'SELECT sum(salary) FROM employees WHERE department_id = :b_dept';
    
    -- We pass the actual value via the USING clause
    EXECUTE IMMEDIATE v_sql INTO v_salary USING v_dept;
    
    DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_salary);
END;

Bulk Processing with NDS

You can combine Dynamic SQL with bulk operations for extreme performance.

DECLARE
    TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
    v_emps t_emp_tab;
    v_sql  VARCHAR2(1000);
BEGIN
    v_sql := 'SELECT * FROM employees WHERE hire_date > :dt';
    
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_emps USING DATE '2025-01-01';
END;

3. The Heavy Lifter: DBMS_SQL

Before EXECUTE IMMEDIATE, developers had to use the DBMS_SQL package. It is vastly more complex, requiring you to manually open a cursor, parse the string, bind variables, execute, fetch, and close the cursor.

Why use it today? Method 4 Dynamic SQL. If you are writing a query tool where you do not know the number of columns, their data types, or the number of bind variables until runtime, EXECUTE IMMEDIATE cannot handle it. DBMS_SQL provides the DESCRIBE_COLUMNS procedure to dynamically interrogate the result set shape.

DECLARE
    v_cursor    INTEGER;
    v_rows      INTEGER;
BEGIN
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, 'UPDATE items SET status = ''A''', DBMS_SQL.NATIVE);
    v_rows := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

4. Defeating SQL Injection

Dynamic SQL is the primary vector for SQL injection in PL/SQL.

Vulnerable Code:

v_sql := 'SELECT * FROM users WHERE username = ''' || p_user || '''';

If p_user is ' OR '1'='1, the query becomes SELECT * FROM users WHERE username = '' OR '1'='1'.

Fix 1: Bind Variables

v_sql := 'SELECT * FROM users WHERE username = :u';
EXECUTE IMMEDIATE v_sql USING p_user;

Fix 2: DBMS_ASSERT for Object Names Since table names must be concatenated, use the DBMS_ASSERT package to verify the object actually exists before executing.

-- Will throw an error if p_table does not exist in the dictionary
v_safe_table := DBMS_ASSERT.SQL_OBJECT_NAME(p_table);
v_sql := 'TRUNCATE TABLE ' || v_safe_table;
EXECUTE IMMEDIATE v_sql;