Use Bind Variables with Dynamic SQL

4.1 Use Bind Arguments with Dynamic SQL

previous|next

You can use bind arguments in the WHERE clause, the VALUES clause, or the SET clause of any SQL statement, as long as the bind arguments are not used as Oracle identifiers (such as column names or table names), or key words.

For example, you can rewrite this dynamic SQL with concatenated string value:

v_stmt :=
'SELECT '||filter(p_column_list)||' FROM employees '||
'WHERE department_name = '''|| p_department_name ||'''';

EXECUTE IMMEDIATE v_stmt;

into this dynamic SQL with a placeholder (:1) using a bind argument (p_department_name):

v_stmt :=
'SELECT '||filter(p_column_list)||' FROM employees '||
'WHERE department_name = :1';

EXECUTE IMMEDIATE v_stmt USING p_department_name;

Developers often use dynamic SQL to handle varying number of IN-list values or LIKE comparison operators in the query condition.

You do not have to use dynamic SQL for these situations. See the examples in lesson 3.1.

For more details on this topic see:
Oracle® Database PL/SQL Language Reference,
Using Dynamic SQL