<div>
the binding can't be dynamic &nbsp;you'll have to determine which binds you need and use a different USING for each<br />
<br />
if a is not null and b is not null then <br />
&nbsp; &nbsp; execute immediate vquery using a,b;<br />
elsif a is not null then<br />
&nbsp; &nbsp; execute immediate vquery using a;<br />
else if b is not null then<br />
&nbsp; &nbsp; execute immediate vquery using b;<br />
else<br />
&nbsp; &nbsp; execute immediate vquery;<br />
end if;
</div>


<div>
thanks sdstuber.. ya.. that's what we've at the moment.. but we've like 6 input parameters and looks like we need all permutations which makes simple procedure too long..<br />
<br />
is there any other way to achive this? 
</div>


<div>
&gt;&gt;&gt; param1=nvl(:a,param1)<br />
<br />
<br />
using a trick like this only works if none of the columns are nullable.
</div>


<div>
thanks.. <br />
<br />
<a href="http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D2369029908548088969&p_cat=JA09_ask%20tom_to_tom.pdf&p_company=822925097021874" rel="ugc">http://asktom.oracle.com/pls/asktom/z?p_url=ASKTOM%2Edownload_file%3Fp_file%3D2369029908548088969&p_cat=JA09_ask%20tom_to_tom.pdf&p_company=822925097021874</a><br />
<br />
it's more like what you guys answered..<br />
<br />
add bind variable like: <br />
<br />
if param1 is not null then <br />
&nbsp;query = query || ' param1 like :param1'<br />
else<br />
&nbsp;query = query || (1=1 or :param1 is null)<br />
<br />

</div>


<div>
ah, that's clever, &nbsp;Tom, as usual, has great technique.<br />
<br />
thanks for posting back, &nbsp;I recommend accepting your own post as the solution here.
</div>


<div>
oops, too late on my recommendation.<br />
<br />
If you'd like, I can reopen the question and you can select your own post.<br />
<br />
I doubt anyone here will dispute the validity of your answer.
</div>


<div>
that's fine.. basically all answers are valid and correct.. the one i posted is another variation.. 
</div>


<div>
actually, yours is significantly different than anything that was posted above<br />
<br />
it's an easily expandable version that allows for dynamic sql but with a constant USING clause<br />
so it avoids the combinatorics I illustrated and it doesn't suffer from the functional or performance problems in the NVL trick.<br />
<br />
<br />
<br />

</div>


<div>
it's up to you though. &nbsp;If you found the other answers helpful, even if you don't use them (and I wouldn't, not even my own) , then you're free to award points as you saw fit.<br />
<br />

</div>


<div>
<div class="content wysiwyg-content">
hi Gurus.. <br />
<br />
execute immediate 'select something from sometable where param1=:a and param2=:b' using a,b;<br />
<br />
sounds good, if i have to dynamically add using clause how to do this? <br />
<br />
for example i've a procedure <br />
<br />
proc (a in varchar2, b in varchar2) <br />
begin<br />
&nbsp; &nbsp;vquery = 'select * from sometable where ';<br />
&nbsp; if (a is not null) then vquery = vquery || ' param1=:a';<br />
&nbsp; if (b is not null) then vquery = vquery || ' param2=:b';<br />
<br />
&nbsp;execute immediate vquery using ????<br />
<br />
<br />
Thanks.
</div>
</div>


hi Gurus.. 

execute immediate 'select something from sometable where param1=:a and param2=:b' using a,b;

sounds good, if i have to dynamically add using clause how to do this? 

for example i've a procedure 

proc (a in varchar2, b in varchar2) 
begin
   vquery = 'select * from sometable where ';
  if (a is not null) then vquery = vquery || ' param1=:a';
  if (b is not null) then vquery = vquery || ' param2=:b';

 execute immediate vquery using ????


Thanks.

dynamic bind variables

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.