Bind variables are related to host variables. Host variables are defined in the host or caller, whereas bind variables accept values from the caller to SQL. In PL/SQL the distinction between bind and host variables disappears.
Bind variables can be used in almost any place in PL/SQL with one exception: bind variables in anonymous PL/SQL blocks cannot appear in conditional compilation directives.
VALUES clauses of static DML statements are automatically made bind variables in PL/SQL.
Before you run off and think that Oracle takes care of everything for you, hang on a minute.
That only applies to static statements; the emphasis in the previous sentence was intentional.
Dynamic SQL is slightly different.
Let’s take a look at five common examples:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
-- -- Example 1: typically found in anonymous PL/SQL blocks -- SELECT * BULK COLLECT INTO ... FROM fridge WHERE product = 'Beer'; -- hard-coded literal -- -- Example 2: typically found in anonymous PL/SQL blocks -- SELECT * BULK COLLECT INTO ... FROM fridge WHERE product = :prod; -- bind variable -- -- Example 3: typically found in PL/SQL modules -- SELECT * BULK COLLECT INTO ... FROM fridge WHERE product = product_in; -- bind variable -- -- Example 4: typically found in PL/SQL modules -- EXECUTE IMMEDIATE 'SELECT * FROM fridge ' || 'WHERE product = ''' || product_in || '''' -- literal: value is embedded in statement BULK COLLECT INTO ... ; -- -- Example 5: typically found in PL/SQL modules -- EXECUTE IMMEDIATE 'SELECT * FROM fridge ' || 'WHERE product = :prod' -- bind variable, because... BULK COLLECT INTO ... USING product_in -- ... we substitute rather than embed the value ;
Hopefully, no one would build dynamic SQL like that as it is open to SQL injections; the package
DBMS_ASSERT offers some basic sanity checks on raw (user) input.
The code is only shown for the purposes of our demonstration of the various options with regard to literals and bind variables.
You can use
DBMS_SQL as an alternative to dynamically build a SQL statement, but we have decided not to show the code for reasons of brevity.
There is sometimes a good reason to utilize
DBMS_SQL instead of native dynamic SQL (NDS).
NDS has to be parsed every time the statement is executed; for complex statements the overhead can be significant.
Parsing can be bypassed with
For instance, when a statement is executed for different values inside a loop, you just have to place the call to
DBMS_SQL.PARSE outside of the loop; the calls to
DBMS_SQL.BIND_VARIABLE need to be placed inside the loop.