Explain dynamic SQL with EXECUTE IMMEDIATE in PL/SQL

How it works

EXECUTE IMMEDIATE compiles and runs a SQL or PL/SQL string built, fully or partially, at runtime — needed whenever a table/column name, or the shape of the statement itself, isn't known until execution (DDL from PL/SQL, a generic utility procedure). USING binds values in positionally rather than concatenating them into the string, which is what keeps dynamic SQL built this way safe from SQL injection — string-concatenating untrusted input directly into the statement is the mistake to avoid.

DECLARE
  v_table_name VARCHAR2(30) := 'EMPLOYEES';
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count;
  DBMS_OUTPUT.PUT_LINE(v_count);

  EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.05 WHERE employee_id = :1'
    USING 100; -- bind variable, not string concatenation
END;
/

Related Questions