asked on

dynamic bind variables

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.

Sean Stuber

the binding can't be dynamic you'll have to determine which binds you need and use a different USING for each

if a is not null and b is not null then
execute immediate vquery using a,b;
elsif a is not null then
execute immediate vquery using a;
else if b is not null then
execute immediate vquery using b;
else
execute immediate vquery;
end if;

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ram_0218

ASKER

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..

is there any other way to achive this?

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

>>> param1=nvl(:a,param1)

using a trick like this only works if none of the columns are nullable.

ram_0218

ASKER

thanks..

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

it's more like what you guys answered..

add bind variable like:

if param1 is not null then
query = query || ' param1 like :param1'
else
query = query || (1=1 or :param1 is null)

Sean Stuber

ah, that's clever, Tom, as usual, has great technique.

thanks for posting back, I recommend accepting your own post as the solution here.

Sean Stuber

oops, too late on my recommendation.

If you'd like, I can reopen the question and you can select your own post.

I doubt anyone here will dispute the validity of your answer.

ram_0218

ASKER

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

Sean Stuber

actually, yours is significantly different than anything that was posted above

it's an easily expandable version that allows for dynamic sql but with a constant USING clause
so it avoids the combinatorics I illustrated and it doesn't suffer from the functional or performance problems in the NVL trick.

Sean Stuber

it's up to you though. 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.