[Chapter 4] 4.4 Binding VariablesChapter 4Native Dynamic SQL in Oracle8i 4.4 Binding VariablesYou 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.4.4.1 Binding Versus ConcatenationIn most situations, you will be able to take two different paths to insert program values into your SQL string: binding and concatenation. The following table contrasts these approaches for a dynamic UPDATE statement.ConcatenationBinding EXECUTE IMMEDIATE 'UPDATE ' || tab 'SET sal = ' || v_sal; EXECUTE IMMEDIATE 'UPDATE ' || tab 'SET sal = :new_sal' USING 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. Two different approaches -- which should you use and when?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 itself does not contain the value, just the placeholder name. Therefore, you can bind different values to the same SQL statement without changing that statement. Since it is the same SQL statement, your application is more likely to be able to take advantage of the pre-parsed cursors that are cached in the System Global Area (SGA) of the database.Binding is easier to write and maintainWhen you bind, you don't have to worry about datatype conversion. It is all handled for you by the NDS engine. Binding, in fact, minimizes datatype conversion, since it works with the native datatypes. If you use concatenation, you will often need to write very complex, error-prone string expressions involving multiple single quotes, TO_DATE and TO_CHAR function calls, and so on. For example, consider the following comparison of concatenation and binding for a more complex statement:/* Binding */ EXECUTE IMMEDIATE 'UPDATE employee SET salary = :val WHERE hire_date BETWEEN :lodate AND :hidate' USING v_start, v_end; /* Concatenation */ EXECUTE IMMEDIATE 'UPDATE employee SET salary = ' || val_in || ' WHERE hire_date BETWEEN ' || ' TO_DATE (''' || TO_CHAR (v_start) || ''')' || ' AND ' || ' TO_DATE (''' || TO_CHAR (v_end) || ''')';So bind whenever possible . . . which leads to the question: when is binding not an option?4.4.2 Limitations on BindingYou can only bind into your SQL statement expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. You cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause). For those parts of your string, you must use concatenation.For example, suppose you want to create a procedure that will truncate the specified view or table. Your first attempt might look something like this:CREATE OR REPLACE PROCEDURE truncobj ( nm IN VARCHAR2, tp IN VARCHAR2 := 'TABLE', sch IN VARCHAR2 := NULL) IS BEGIN EXECUTE IMMEDIATE 'TRUNCATE :trunc_type :obj_name' USING tp, NVL (sch, USER) || '.' || nm; END; /This code seems perfectly reasonable. But when you try to run the procedure you'll get this error:ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keywordAnd if you rewrite the procedure to simply truncate tables, as follows:EXECUTE IMMEDIATE 'TRUNCATE TABLE :obj_name' USING nm;Then the error becomes:ORA-00903: invalid table nameWhy does NDS (and DBMS_SQL) have this restriction? When you pass a string to EXECUTE IMMEDIATE, the runtime engine must first parse the statement. The parse phase guarantees that the SQL statement is properly defined. PL/SQL can tell that the following statement is valid:'UPDATE emp SET sal = :xyz'without having to know the value of :xyz. But how can PL/SQL know if the following statement is well formed?'UPDATE emp SET :col_name = :xyz'Even if you don't pass in nonsense for col_name, it won't work. For that reason, you must use concatenation:CREATE OR REPLACE PROCEDURE truncobj ( nm IN VARCHAR2, tp IN VARCHAR2 := 'TABLE', sch IN VARCHAR2 := NULL) IS BEGIN EXECUTE IMMEDIATE 'TRUNCATE ' || tp || ' ' || NVL (sch, USER) || '.' || nm; END; /4.4.3 Argument Modes Bind arguments can have one of three modes:INRead-only value (the default mode)OUTWrite-only variableIN OUTCan read the value coming in and write the value going outWhen you are executing a dynamic query, all bind arguments must be IN mode, except when you are taking advantage of the RETURNING clause, as shown here:CREATE OR REPLACE PROCEDURE wrong_incentive ( company_in IN INTEGER, new_layoffs IN NUMBER ) IS sql_string VARCHAR2(2000); sal_after_layoffs NUMBER; BEGIN sql_string := 'UPDATE ceo_compensation SET salary = salary + 10 * :layoffs WHERE company_id = :company RETURNING salary INTO :newsal'; EXECUTE IMMEDIATE sql_string USING new_layoffs, company_in, OUT sal_after_layoffs; DBMS_OUTPUT.PUT_LINE ( 'Benefiting from the misery of others at $' || sal_after_layoffs); END;Besides being used with the RETURNING clause, OUT and IN OUT bind arguments come into play mostly when you are executing dynamic PL/SQL. In this case, the modes of the bind arguments must match the modes of any PL/SQL program parameters, as well as the usage of variables in the dynamic PL/SQL block. Let's take a look at how this works with a few examples. Suppose that I have created the following stored procedure (I am writing this text in May 1999, as Kosovar Albanians are being pushed from their homes by Milosevic, and NATO bombs ravage Yugoslavia): /* Filename on companion disk: natotarg.sql */ PROCEDURE pick_nato_targets ( media_outlet_ok IN BOOLEAN, electric_grid_ok IN BOOLEAN, maternity_ward_ok IN BOOLEAN, cumulative_regrets IN OUT NUMBER, civilian_casualities OUT NUMBER )Now I will just wander kind of naively into the territory of dynamic PL/SQL and execute the procedure, as follows:BEGIN EXECUTE IMMEDIATE 'BEGIN pick_nato_targets (TRUE, TRUE, TRUE, 10, 100); END;'; END; /Since cumulative_regrets is an IN OUT argument, however, I get these errors:PLS-00363: expression '10' cannot be used as an assignment target PLS-00363: expression '100' cannot be used as an assignment targetThe procedure wants to pass back a value through the last two arguments. I need to provide a data structure to hold those values. Literals will not do, so I change it to this:DECLARE next_to_old_defense_building BOOLEAN := TRUE; we_all_make_mistakes NUMBER; others_die_for_them NUMBER; BEGIN EXECUTE IMMEDIATE 'BEGIN pick_nato_targets ( TRUE, TRUE, :baby_place, :whoops, :it_happens); END;' USING next_to_old_defense_building, we_all_make_mistakes, others_die_for_them; END; /And now I get the following error:PLS-00457: in USING clause, expressions have to be of SQL typeswhich reminds me that even though I am running dynamic PL/SQL, I have to conform to the rules and restrictions of NDS: only SQL datatypes are allowed, and Boolean is still not one of them, though I sure don't understand why.But, fine, I will not pass in the Boolean value; I'll stick to numeric bind values: EXECUTE IMMEDIATE 'BEGIN pick_nato_targets ( TRUE, TRUE, TRUE, :whoops, :it_happens); END;' USING we_all_make_mistakes, others_die_for_them;But then I get this error:ORA-06536: IN bind variable bound to an OUT positionI have left both bind arguments with the default IN mode, and that does not match the arguments. And if I change them both to OUT:USING OUT we_all_make_mistakes, OUT others_die_for_them;I get this error:ORA-06537: OUT bind variable bound to an IN positionThat would seem to be darn confusing, but the reality is that when you have an IN OUT argument, the error message treats it as if it's an IN argument.And so we find that the only way to call this procedure successfully in NDS is with the following statement:EXECUTE IMMEDIATE 'BEGIN pick_nato_targets ( TRUE, TRUE, FALSE, :whoops, :it_happens); END;' USING IN OUT we_all_make_mistakes, OUT others_die_for_them;4.4.4 Duplicate PlaceholdersIn a dynamically constructed and executed SQL string, NDS associates placeholders with USING clause bind arguments by position, rather than by name. The treatment of multiple placeholders with the same name varies, however, according to whether you are using dynamic SQL or dynamic PL/SQL. You need to follow these rules:When you are executing a dynamic SQL string (DML or DDL; in other words, the string does not end in a semicolon), you must supply an argument for each placeholder, even if there are duplicates.When you are executing a dynamic PL/SQL block (the string ends in a semicolon), you supply an argument for each unique placeholder.4.4.5 Passing NULL ValuesWe will all encounter special moments when we want to pass a NULL value as a bind argument, as follows:EXECUTE IMMEDIATE 'UPDATE employee SET salary = :newsal WHERE hire_date IS NULL' USING NULL;You will, however, get this error:PLS-00457: in USING clause, expressions have to be of SQL typesBasically, what this is saying is that NULL has no datatype, and "no datatype" is not a valid SQL datatype. So what are you supposed to do if you need to pass in a NULL value? You can do one of two things:Hide it behind a variable façade, most easily done with an uninitialized variable, as shown here: DECLARE /* Default initial value is NULL */ no_salary_when_fired NUMBER; BEGIN EXECUTE IMMEDIATE 'UPDATE employee SET salary = :newsal WHERE hire_date IS NULL' USING no_salary_when_fired; END;Use a conversion function to convert the NULL value to a typed value explicitly:BEGIN EXECUTE IMMEDIATE 'UPDATE employee SET salary = :newsal WHERE hire_date IS NULL' USING TO_NUMBER (NULL); END;4.3 Multirow Queries with Cursor Variables4.5 Working with Objects and Collections Copyright (c) 2000 O'Reilly & Associates. All rights reserved.