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

When You Need Dynamic SQL

Most PL/SQL uses static SQL where the statement is known at compile time. Dynamic SQL is needed for DDL operations, queries where table or column names vary, or WHERE clause structures that change based on input.

Basic EXECUTE IMMEDIATE

EXECUTE IMMEDIATE 'CREATE TABLE temp_data (id NUMBER, val VARCHAR2(100))';

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;
END;

Always Use Bind Variables for Values

Never concatenate user-supplied values into SQL strings. Always use bind variables:

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

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

-- Multiple binds
EXECUTE IMMEDIATE
    'UPDATE employees SET salary = :1 WHERE department_id = :2'
    USING p_new_salary, p_dept_id;

Protecting Identifiers With DBMS_ASSERT

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;

Dynamic SQL With BULK Operations

EXECUTE IMMEDIATE
    'SELECT first_name FROM employees WHERE department_id = :1'
    BULK COLLECT INTO l_names USING p_dept_id;

When to Avoid Dynamic SQL

Dynamic SQL is harder to read, debug, and more vulnerable to injection. Use static SQL whenever possible. A common anti-pattern is building dynamic WHERE clauses when static NVL patterns work: WHERE (:p_dept IS NULL OR department_id = :p_dept). This is safer, faster, and easier to maintain.