PL/SQL Variables

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.

Variables in WHERE and 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 -- Example 1: typically found in anonymous PL/SQL blocks
 3 --
 4 SELECT
 5   *
 6 BULK COLLECT INTO
 7   ...
 8 FROM
 9   fridge
10 WHERE
11   product = 'Beer';                -- hard-coded literal
12
13 --
14 -- Example 2: typically found in anonymous PL/SQL blocks
15 --
16 SELECT
17   *
18 BULK COLLECT INTO
19   ...
20 FROM
21   fridge
22 WHERE
23   product = :prod;                 -- bind variable
24
25 --
26 -- Example 3: typically found in PL/SQL modules
27 --
28 SELECT
29   *
30 BULK COLLECT INTO
31   ...
32 FROM
33   fridge
34 WHERE
35   product = product_in;            -- bind variable
36
37 --
38 -- Example 4: typically found in PL/SQL modules
39 --
40 EXECUTE IMMEDIATE
41   'SELECT * FROM fridge ' ||
42   'WHERE product = ''' ||
43   product_in || ''''               -- literal: value is embedded in statement
44   BULK COLLECT INTO ...
45 ;
46
47 --
48 -- Example 5: typically found in PL/SQL modules
49 --
50 EXECUTE IMMEDIATE
51   'SELECT * FROM fridge ' ||
52   'WHERE product = :prod'          -- bind variable, because...
53   BULK COLLECT INTO ...
54   USING product_in                 -- ... we substitute rather than embed the value
55 ;

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 DBMS_SQL. 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.