[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

dynamic bind variables

Posted on 2011-10-03
12
Medium Priority
?
620 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:ram_0218
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36903594
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 36903597
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
 
LVL 17

Author Comment

by:ram_0218
ID: 36903608
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 600 total points
ID: 36903620
try

execute immediate 'select something from sometable where param1=nvl(:a,param1) and param2=nvl(:b,param2)' using a,b
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 800 total points
ID: 36903634
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36903677
>>> param1=nvl(:a,param1)


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

Author Comment

by:ram_0218
ID: 36904070
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36904094
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36904143
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
 
LVL 17

Author Comment

by:ram_0218
ID: 36904214
that's fine.. basically all answers are valid and correct.. the one i posted is another variation..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36904300
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36904308
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question