You 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.
In 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.
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:
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.
When 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) || ''')';
You 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 keyword
And 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 name
Why 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'
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; /
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 target
The 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 types
which 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 position
I 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 position
That 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.
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;
In 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.
We 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 types
Basically, 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:
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;
BEGIN EXECUTE IMMEDIATE 'UPDATE employee SET salary = :newsal WHERE hire_date IS NULL' USING TO_NUMBER (NULL); END;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.