Effective Use of EXECUTE IMMEDIATE and Dynamic SQL in PL/SQL

When You Need Dynamic SQL

Most PL/SQL code uses static SQL, where the complete SQL statement is known at compile time. But some situations require dynamic SQL, where the statement is constructed at runtime: DDL operations (CREATE, ALTER, DROP), queries where the table name or column list varies, or situations where the WHERE clause structure changes based on user input. PL/SQL’s EXECUTE IMMEDIATE and DBMS_SQL handle these cases.

Basic EXECUTE IMMEDIATE

-- DDL: Cannot be done in static SQL
EXECUTE IMMEDIATE 'CREATE TABLE temp_data (id NUMBER, val VARCHAR2(100))';

-- Dynamic query with bind variable
DECLARE
    l_count NUMBER;
    l_table VARCHAR2(128) := 'EMPLOYEES';
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(l_table)
        INTO l_count;
    DBMS_OUTPUT.PUT_LINE('Row count: ' || l_count);
END;

Always Use Bind Variables for Values

The most critical rule in dynamic SQL: never concatenate user-supplied values directly into SQL strings. Always use bind variables with the USING clause:

-- WRONG: SQL injection vulnerability
EXECUTE IMMEDIATE 'DELETE FROM orders WHERE status = ''' || p_status || '''';

-- CORRECT: Use bind variables
EXECUTE IMMEDIATE 'DELETE FROM orders WHERE status = :1' USING p_status;

-- CORRECT: Multiple bind variables
EXECUTE IMMEDIATE
    'UPDATE employees SET salary = :1 WHERE department_id = :2 AND hire_date > :3'
    USING p_new_salary, p_dept_id, p_date_threshold;

Bind variables prevent SQL injection, improve performance through cursor sharing, and make your code cleaner. The only things that cannot be bound are identifiers (table names, column names) and SQL keywords, which must be validated using DBMS_ASSERT.

Protecting Against SQL Injection in Identifiers

When table or column names come from user input, use DBMS_ASSERT to validate them:

DECLARE
    l_table  VARCHAR2(128);
    l_column VARCHAR2(128);
BEGIN
    -- Validates that the input is a real SQL object name
    l_table  := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);
    l_column := DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name);

    EXECUTE IMMEDIATE
        'SELECT ' || l_column || ' FROM ' || l_table || ' WHERE rownum = 1'
        INTO l_value;
END;

Dynamic SQL With BULK Operations

DECLARE
    TYPE t_names IS TABLE OF VARCHAR2(100);
    l_names t_names;
BEGIN
    EXECUTE IMMEDIATE
        'SELECT first_name FROM employees WHERE department_id = :1'
        BULK COLLECT INTO l_names
        USING p_dept_id;
END;

When to Avoid Dynamic SQL

Dynamic SQL is harder to read, harder to debug (no compile time checking), and more vulnerable to injection if not written carefully. Use static SQL whenever possible. A common anti-pattern is building dynamic WHERE clauses when a static query with NVL or CASE would work: WHERE (:p_dept_id IS NULL OR department_id = :p_dept_id). This static approach is safer, faster, and easier to maintain.