Building and executing SQL at runtime

Sometimes you don't know the SQL statement until runtime — maybe the table name comes from a parameter, or the WHERE clause is built dynamically. EXECUTE IMMEDIATE is PL/SQL's tool for this.

Recipe #1 - Basic EXECUTE IMMEDIATE

DECLARE
  v_table VARCHAR2(30) := 'EMP';
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table
  INTO v_count;

  DBMS_OUTPUT.put_line(v_table || ' has ' || v_count || ' rows.');
END;

Recipe #2 - Using bind variables (the safe way)

Never concatenate user input directly into SQL strings — that's how SQL injection happens. Use bind variables instead:

DECLARE
  v_deptno NUMBER := 10;
  v_count  NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM emp WHERE deptno = :dept'
  INTO v_count
  USING v_deptno;

  DBMS_OUTPUT.put_line('Department ' || v_deptno
    || ' has ' || v_count || ' employees.');
END;

Recipe #3 - Dynamic DML with RETURNING

Execute an UPDATE and get back the modified values:

DECLARE
  v_new_sal NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE emp SET sal = sal * 1.10
     WHERE empno = :id
     RETURNING sal INTO :new_sal'
  USING 7839
  RETURNING INTO v_new_sal;

  DBMS_OUTPUT.put_line('New salary: ' || v_new_sal);
  COMMIT;
END;

Recipe #4 - Dynamic DDL

DDL statements don't support bind variables — you have to concatenate, but validate the input first:

CREATE OR REPLACE PROCEDURE create_backup_table(
  p_table_name VARCHAR2
) AS
  v_clean_name VARCHAR2(30);
BEGIN
  -- Validate: only allow alphanumeric and underscore
  v_clean_name := REGEXP_REPLACE(UPPER(p_table_name), '[^A-Z0-9_]', '');

  EXECUTE IMMEDIATE
    'CREATE TABLE ' || v_clean_name || '_BAK AS SELECT * FROM '
    || v_clean_name;

  DBMS_OUTPUT.put_line('Backup table created: '
    || v_clean_name || '_BAK');
END;

Recipe #5 - Dynamic cursor with OPEN FOR

When you need to loop through dynamic query results:

DECLARE
  TYPE t_ref_cur IS REF CURSOR;
  l_cursor t_ref_cur;
  l_ename  VARCHAR2(100);
  l_sal    NUMBER;
BEGIN
  OPEN l_cursor FOR
    'SELECT ename, sal FROM emp WHERE deptno = :dept ORDER BY sal DESC'
  USING 20;

  LOOP
    FETCH l_cursor INTO l_ename, l_sal;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.put_line(l_ename || ': ' || l_sal);
  END LOOP;

  CLOSE l_cursor;
END;

Rule of thumb: Use static SQL whenever possible. Dynamic SQL is harder to debug, can't be checked at compile time, and is vulnerable to injection if you're not careful. But when you genuinely need it — variable table names, dynamic WHERE clauses, runtime DDL — EXECUTE IMMEDIATE is the right tool.