Re: Bind Variables

"Jack Dawson" <srik_at_cnet.com> wrote in message news:<9s9ld7$e93$1_at_innbox.cnet.com>...
> I have been debating the answer for this question.... but not really into a
> solution,,,,
>
> When to and When not to use bind variables???
>
> When to and When not to placeholder columns(&) ?/
>
> Thanks
> Jack....

I assume you're referring to SQL-Plus scripts that you want to execute with some user input. If your situation is different, than I only confirm what Matt wrote.

As a rule of thumb I do use substitution variables in SQL scripts if I do not care for the type of the variable. And because substitution variables are replaced nearly everywhere even in the SPOOL command. If you just want to output the value of the variable you can imbed it in a normal sentence using PROMPT while you're bound to the table-like output if you PRINT a bind variable. You can also do a kind of dynymic SQL if you fill a string like substiution variable with a complete ORDER BY or WHERE clause. Think of a query with the following filter:   WHERE colA in ( ... )
If you have a substitution variable subvar with a value of 1,2,4,5 you can use later on

   ... WHERE colA in (&subvar);
and it will work, while

   ... WHERE colA in (:v_bind)
will cause a SQL parsing error.
Hm, to be precise, the error will already happen, when you try to get something like 1,2,4,5 into a bind variable, you may mask it as a string '1,2,4,5', but this is not what you want. In general, bind variables in SQL-statements are only allowed at places where you usually would have a literal numeric or string value.
Furthermore you can just turn VERIFY ON to see what happens to your substitution variable.

If you plan to change from interactive user input to command line arguments, a bind variable is a good idea, because then the substitution variable is not spread over the whole file and you only have to replace the substitution variable with the argument &1 at the line where you copy the substitution in the bind variable.

BEGIN :v_bind := &1; END;
/
instead of
BEGIN :v_bind := &subvar; END;
/

• Transfer values from substitution to bind variables and vice versa

If you have a line with ACCEPT in your script to prompt for user input and you need to do more evaluation than just to check if the format is correct, it's easier to have it in a bind variable and do all the necessary things inside an PL/SQL-Block where you can use procedural logic like IF .. ELSE. To copy the value from the substitution into the bind variable is straight forward:

BEGIN
  :v_dummy := &dummy;
  SELECT &other INTO :v_bind FROM DUAL;
    ...
END;
/

To do it the other way round is more tricky because you need to know some of the rarely used features of the SQL*Plus command COLUMN.

variable v_bind VARCHAR2(20)
BEGIN :v_bind := 'Two words'; END;
/
COLUMN dummy NEW_VALUE sub_var
SELECT :v_bind as dummy FROM dual;
PROMPT What is in sub_var: &&sub_var

Hope this helps..

Rolf. Received on Thu Nov 08 2001 - 11:59:01 CET