3.3 What If You Must Use Dynamic SQL?

Dynamic SQL may be unavoidable in the following types of situations:

• You do not know the full text of the SQL statements that must be executed in a PL/SQL procedure. For example, a SELECT statement that includes an identifier (such as table name) that is unknown at compile time or a WHERE clause in which the number of subclauses is unknown at compile time.
• You want to execute DDL statements and other SQL statements that are not supported in purely static SQL programs.
• You want to write a program that can handle changes in data definitions without the need to recompile.

If you must use dynamic SQL, try not to construct it through concatenation of input values. Instead, use bind arguments.

If you cannot avoid input concatenation, you must validate input values, and also consider constraining user input to a predefined list of values, preferably numeric values. Lesson 5 addresses input filtering and sanitizing in more detail.