Oracle PL/SQL Programming, Third Edition

Binding Variables

You have seen several examples of the use of bind variables or arguments with NDS. Let’s now go over the various rules and special situations you may encounter when binding.

Binding Versus Concatenation

In most situations, you can take two different paths to insert program values into your SQLstring: binding and concatenation. The following table contrasts these approaches for a dynamic UPDATE statement.

Binding	Concatenation
EXECUTE IMMEDIATE 'UPDATE ' || tab 'SET sal = :new_sal' USING v_sal;	EXECUTE IMMEDIATE 'UPDATE ' || tab 'SET sal = ' || v_sal;

Binding involves the use of placeholders and the USING clause; concatenation shortcuts that process by adding the values directly to the SQL string. When should you use each approach? I recommend that you bind arguments whenever possible (see the next section for limitations on binding) rather than rely on concatenation. There are two reasons for taking this approach:

Binding is faster

When you bind in a value, the SQL string does not contain the value, just the placeholder name. Therefore, you can bind different values to the same SQL statement without changing that statement. Because it is the same SQL statement, your application can more likely take advantage of the preparsed cursors that are cached in the System Global Area (SGA) of the database.

Binding is easier to write and maintain

When you bind, you don’t have to worry about datatype conversion; it is all handled for you by the NDS engine. In fact, ...