Home » SQL & PL/SQL » SQL & PL/SQL » Using Binds in Dynamic SQL
Using Binds in Dynamic SQL [message #216849] Tue, 30 January 2007 12:56 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I have been using the OPEN...FOR...USING functionality to run dynamic SQL statements using variables. As I originally understood it I thought this would do binding based on the name of the bind variable. IE if you have the following dynamic SQL:

select tab1.a, tab2.b, tab2.c 
from tab1, tab2
where tab1.tab2_id = tab2.id
and (tab1.param = :bind1 or tab2.param = :bind1)


You could execute it using one bind and that one bind would be assigned to both instances of :bind1, in practice it would seem this is not the case and that I need to pass in two values for each instance of :bind1.

Is this in fact the case or am I perhaps doing something wrong to make this the case? And if it is, is there any way to bind so the bind variable names come into play short of scanning the SQL looking for bind variables and doing replaces of them?

Andrew
Re: Using Binds in Dynamic SQL [message #216886 is a reply to message #216849] Tue, 30 January 2007 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
The following is an excerpt from Oracle online documentation.

Using Duplicate Placeholders with Dynamic SQL

Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. If you specify a sequence of placeholders like :a, :a, :b, :b, you must include four items in the USING clause. For example, given the dynamic string

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

the fact that the name X is repeated is not significant. You can code the corresponding USING clause with four different bind variables:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the USING clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the USING clause. In Example 7-7, all references to the placeholder x are associated with the first bind argument a, and the second unique placeholder y is associated with the second bind argument b.

Example 7-7 Using Duplicate Placeholders With Dynamic SQL

CREATE PROCEDURE calc_stats(w NUMBER, x NUMBER, y NUMBER, z NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
   a NUMBER := 4;
   b NUMBER := 7;
   plsql_block VARCHAR2(100);
BEGIN
   plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
   EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/

Re: Using Binds in Dynamic SQL [message #217031 is a reply to message #216886] Wed, 31 January 2007 07:05 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Thank you VERY much for that, because in this particular program all I am doing is looping through a result set which comes from this SQL query and dumping it to an output in a certain format, this should easily be replicable using an anonymous block.
Re: Using Binds in Dynamic SQL [message #217034 is a reply to message #216849] Wed, 31 January 2007 07:21 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
Just updated my code to work that way and everything is looking good. Thanks again for the help there, my first task this morning was going to be parsing the SQL statement to do replaces on all the bind variables, I like this way a good deal more.
Previous Topic: Accessing oracle 10g xe from a client
Next Topic: Help with query (first_value)
Goto Forum:
  


Current Time: Tue Dec 06 16:20:13 CST 2016

Total time taken to generate the page: 0.12182 seconds