1

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)?

  • I dont know what you exactly want to know but it seems to be about bind variables. Is it really necessary to demonstrate your quesion that you show us such a complicated piece of code including a delete statement, an insert statement, a join, a subselect, a for loop. Isn't it possible to show us your problem using one table and one select? – miracle173 Jan 7 '17 at 14:34
3

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.