Dynamic SQL

Dynamic SQL is a SQL statement that is constructed and executed at program execution time. In contrast to this, static SQL statements are hard-coded in the program and executed "as-is" at run-time. Dynamic SQL provides more flexibility, nevertheless, static SQL is faster and more secure than dynamic SQL.

Starting from Oracle 8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:

EXECUTE IMMEDIATE is often used in the maintenance code that automatically alters tables, indexes, etc. because in Embedded SQL you cannot specify the object name as a variable (Oracle needs to pre-compile your query and it cannot do this with unspecified object name). When you create a dynamic SQL, your query is compiled at runtime.
EXECUTE IMMEDIATE is often faster than DBMS_SQL and it should be your first choice when static SQL is not enough.

If your query grows a lot or gets very dynamic (ex. has variable number of parameters) then you might reconsider slower option: built-in package DBMS_SQL. Note that it is different from the PL/SQL coding pattern and is might make your code more complicated.