Dynamic SQL (EXECUTE IMMEDIATE & DBMS_SQL)

Dynamic SQL lets you build and execute SQL statements at runtime — when the table name, column list, or conditions are not known until execution. Oracle provides two mechanisms: EXECUTE IMMEDIATE (the modern, simpler approach) and DBMS_SQL (for advanced scenarios where the query structure itself is determined at runtime).

EXECUTE IMMEDIATE — One-Shot Execution

The standard way to run dynamically built SQL:

DECLARE
    v_sql     VARCHAR2(4000);
    v_count   NUMBER;
BEGIN
    -- DDL — no bind variables needed
    v_sql := 'CREATE TABLE temp_emp_backup AS SELECT * FROM employees WHERE 1=0';
    EXECUTE IMMEDIATE v_sql;
    DBMS_OUTPUT.PUT_LINE('Backup table created.');

    -- DML with literal (simple cases)
    EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_emp_backup';
END;
/

USING — Bind Variables for DML/Queries

Always use bind variables for data values to prevent SQL injection and improve performance:

DECLARE
    v_dept_id  NUMBER := 80;
    v_min_sal  NUMBER := 10000;
    v_count    NUMBER;
BEGIN
    -- DML with bind variables
    EXECUTE IMMEDIATE
        'UPDATE employees SET salary = salary * 1.05
         WHERE department_id = :dept AND salary < :min_sal'
    USING v_dept_id, v_min_sal;   -- positional bind

    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
    COMMIT;

    -- Query with INTO and USING
    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM employees WHERE department_id = :1'
    INTO  v_count
    USING v_dept_id;

    DBMS_OUTPUT.PUT_LINE('Dept ' || v_dept_id || ' count: ' || v_count);
END;
/

OUT and IN OUT Bind Variables

For DML with RETURNING or OUT parameters:

DECLARE
    v_emp_id  NUMBER := 100;
    v_new_sal NUMBER;
BEGIN
    EXECUTE IMMEDIATE
        'UPDATE employees
         SET    salary = salary * 1.10
         WHERE  employee_id = :emp_id
         RETURNING salary INTO :new_sal'
    USING     v_emp_id
    RETURNING INTO v_new_sal;

    DBMS_OUTPUT.PUT_LINE('New salary: $' || v_new_sal);
    COMMIT;
END;
/

Dynamic Query into a REF CURSOR

Combine dynamic SQL with a REF cursor for flexible multi-row results:

CREATE OR REPLACE PROCEDURE query_employees(
    p_where_clause IN  VARCHAR2,    -- caller-supplied filter (pre-validated)
    p_cursor       OUT SYS_REFCURSOR
) IS
BEGIN
    -- NEVER concatenate raw user input — validate or use DBMS_ASSERT first
    OPEN p_cursor FOR
        'SELECT employee_id, first_name, last_name, salary
         FROM   employees
         WHERE  ' || p_where_clause
         || ' ORDER BY employee_id';
END query_employees;
/

BULK COLLECT with EXECUTE IMMEDIATE

DECLARE
    TYPE t_name_list IS TABLE OF VARCHAR2(100);
    v_names   t_name_list;
    v_dept_id NUMBER := 90;
BEGIN
    EXECUTE IMMEDIATE
        'SELECT first_name || '' '' || last_name
         FROM   employees
         WHERE  department_id = :d
         ORDER BY last_name'
    BULK COLLECT INTO v_names
    USING v_dept_id;

    FOR i IN 1..v_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_names(i));
    END LOOP;
END;
/

Dynamic Table Name — Common Pattern

CREATE OR REPLACE PROCEDURE copy_to_backup(p_table_name IN VARCHAR2) IS
    v_safe_table  VARCHAR2(128);
    v_sql         VARCHAR2(500);
    v_count       NUMBER;
BEGIN
    -- Validate table name to prevent injection
    v_safe_table := DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);

    -- Check it exists as a table
    SELECT COUNT(*) INTO v_count
    FROM   user_tables WHERE table_name = UPPER(v_safe_table);

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20301, 'Table ' || v_safe_table || ' not found');
    END IF;

    v_sql := 'INSERT INTO ' || v_safe_table || '_backup SELECT * FROM ' || v_safe_table;
    EXECUTE IMMEDIATE v_sql;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows copied to ' || v_safe_table || '_backup');
    COMMIT;
END copy_to_backup;
/

⚠

SQL Injection: Never concatenate unvalidated user input into dynamic SQL. Use bind variables (USING) for values. For object names (tables, columns), validate with DBMS_ASSERT.SQL_OBJECT_NAME or DBMS_ASSERT.SIMPLE_SQL_NAME.

DBMS_SQL — Advanced Dynamic SQL

Use DBMS_SQL when:

The number of bind variables or columns is not known until runtime.
You need to describe and bind dynamically-constructed queries.
The bind variable list is built programmatically (e.g., from a configuration table).

DECLARE
    v_cursor   INTEGER;
    v_sql      VARCHAR2(500);
    v_emp_id   NUMBER;
    v_name     VARCHAR2(100);
    v_salary   NUMBER;
    v_rows     INTEGER;
    v_dept_id  NUMBER := 80;
BEGIN
    v_sql := 'SELECT employee_id, first_name || '' '' || last_name, salary
              FROM   employees
              WHERE  department_id = :dept
              ORDER BY salary DESC
              FETCH FIRST 5 ROWS ONLY';

    -- Open cursor
    v_cursor := DBMS_SQL.OPEN_CURSOR;

    -- Parse
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

    -- Bind variable
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept', v_dept_id);

    -- Define output columns by position
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_emp_id);
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, v_name, 100);
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 3, v_salary);

    -- Execute
    v_rows := DBMS_SQL.EXECUTE(v_cursor);

    -- Fetch
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
        DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_emp_id);
        DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_name);
        DBMS_SQL.COLUMN_VALUE(v_cursor, 3, v_salary);
        DBMS_OUTPUT.PUT_LINE(v_name || ': $' || v_salary);
    END LOOP;

    -- Close
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END;
/

EXECUTE IMMEDIATE vs DBMS_SQL — when to use which?

Use EXECUTE IMMEDIATE when:

The SQL is fully built before execution (even if built dynamically).
The number of bind variables and output columns is fixed in your code.
You want simpler, more readable code.

Use DBMS_SQL when:

The number of bind variables changes at runtime (e.g., a WHERE clause built from a variable-length list).
You need to describe the result columns dynamically (e.g., a generic query framework).
You are converting a DBMS_SQL cursor to a REF CURSOR using DBMS_SQL.TO_REFCURSOR.

For the vast majority of dynamic SQL needs, EXECUTE IMMEDIATE is sufficient and far simpler.

Summary

EXECUTE IMMEDIATE 'sql' — executes any SQL or PL/SQL block dynamically.
USING bind_vars — passes values safely; prevents SQL injection and enables soft parsing.
INTO vars after the SQL string — receives single-row query results.
RETURNING INTO — captures DML output values.
BULK COLLECT INTO — fetches multiple rows from a dynamic query.
Validate object names with DBMS_ASSERT when they cannot be bind-variable-bound.
DBMS_SQL for advanced scenarios: variable bind lists, dynamic column description, cursor conversion.
Never concatenate raw user input — always bind or validate.