Oracle – Bind Variable vs. String-Concatenated Variable

dynamic-sqloracleplsql

I've made an anonymous block that has dynamic SQL:

01    DECLARE
02        l_table_name VARCHAR2(100);
03        l_field_name VARCHAR2(100);
04        l_domain_name VARCHAR2(100);
05    
06    BEGIN    
07        FOR list_fields IN (
08            SELECT 
09                TABLE_NAME
10                ,FIELD_NAME
11                ,DOMAIN_NAME 
12            FROM 
13                ENG.FIELD_DOMAIN_ENG_VW 
14            WHERE 
15                TABLE_NAME NOT LIKE '%ANNO%' 
16            )
17        LOOP
18            l_table_name := list_fields.TABLE_NAME;
19            l_field_name := list_fields.FIELD_NAME;
20            l_domain_name := list_fields.DOMAIN_NAME;
21    
22            EXECUTE IMMEDIATE
23            'INSERT INTO ENG.CV_ERRORS
24            (TABLE_NAME, FIELD_NAME, ERROR)
25            SELECT
26                :bv1 AS TABLE_NAME
27                ,:bv2 AS FIELD_NAME                 --<<-- Lines in
28                , ' || l_field_name || ' AS ERROR   --<<-- question.
29            FROM ' || 
30                l_table_name ||
31                ' LEFT JOIN
32                (
33                SELECT CODE
34                FROM  ENG.D_CV_ENG_VW
35                WHERE DOMAIN = :bv3
36                )
37                ON ' || l_field_name || ' = CODE
40            WHERE
41                ' || l_field_name || ' IS NOT NULL
42                AND 
43                CODE IS NULL'
44    
45            USING l_table_name, l_field_name, l_domain_name;
46    
47        END LOOP;
48    COMMIT;
49    END;

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |    ERROR    |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
| TABLE_1    | FIELD_2    | zzzz        |
| TABLE_2    | FIELD_1    | asdf        |
+------------+------------+-------------+

There's a part of it that I don't understand how/why it works.

27        ,:bv2 AS FIELD_NAME
28        , ' || l_field_name || ' AS ERROR

Line 27 uses a bind variable that is tied to the l_field_name variable. The value that is returned is the name of the field (example: FIELD_1, FIELD_2, FIELD_1).

Line 28 breaks up the string, and uses the l_field_name variable directly (via concatenation). The value that is returned is the value of the field (example: BLACK SHEEP, zzzz, asdf).

Why do the bind variable (line 27) and the string-concatenated variable (line 28) return different values, when they reference the same thing (l_field_name)?

Best Answer

You ask, referring to the variable l_field_name:

Why do the bind variable (line 27) and the string-concatenated variable (line 28) return different values, when they reference the same thing?

One way to look at this is to consider when and how the value of l_field_name is used.

The first (in terms of time) use is when you compose the string value that represents the INSERT statement. At that time the value of l_field_name is concatenated, along with other string literals and variable values, into the resultant string and becomes a part of it. The complete statement string is then passed to EXECUTE IMMEDIATE for parsing, compilation, and eventual execution.

The second use is when the value of l_field_name is assigned to the host (bind) variable :bv2 just prior to the statement execution. In this case that value is not concatenated or "injected" into the statement text: by this time the statement is already compiled into a plan, and :bv2 behaves like a variable it is -- it holds its value (that is now equal to the value of l_field_name) and passes it on to be inserted into the corresponding column.

Related Question