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.
LVL 17
ram_0218Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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;
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can't do that, from what I know.
unless you ensure that your SQL always uses the same number of arguments, somehow, I don't see how you could do this.
0
ram_0218Author Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HainKurtSr. System AnalystCommented:
try

execute immediate 'select something from sometable where param1=nvl(:a,param1) and param2=nvl(:b,param2)' using a,b
0
sdstuberCommented:
you could use dbms_sql  but I can't say that will actually be easier.

Try double checking your parameters,  I've had to do similar things with massive permutations and I've often found that every possible combination wasn't really necessary or sometimes you can derive your own variable.

For instance, if you have start/end dates, and one of them is null,  rather than eliminating that variable,  default it.
if there is no start date,  use to_date('1','j')   if no end date use to_date('99991231235959','yyyymmddhh24miss')

there may be other variables that are always in pairs or triples.  So if one is present they all are.

No guarantee, but it's worth investigating.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
>>> param1=nvl(:a,param1)


using a trick like this only works if none of the columns are nullable.
0
ram_0218Author Commented:
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)

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

thanks for posting back,  I recommend accepting your own post as the solution here.
0
sdstuberCommented:
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.
0
ram_0218Author Commented:
that's fine.. basically all answers are valid and correct.. the one i posted is another variation..
0
sdstuberCommented:
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.



0
sdstuberCommented:
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.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.