ram_0218
asked on
using IN operator with USING clause
in regards to this:
https://www.experts-exchange.com/questions/27377089/dynamic-bind-variables.html
I've the procedure:
proc(param1 varchar2, param2 varchar2)
open ref_cursor for
'select * from sometable where param1 like :param1 and param2 in :param2'
using '%'||param1||'%', '('||param2||')'
LIKE operator works fine for param1 but IN operator won't work for param2.. Tried all possible input scenarios..
i'm calling the procedure in sqlplus like this:
exec :v:=proc('something','''id 1'',''id2' '')
tried also
proc('something','id1'','' id2')
no errors but always zero records output..but there are matching records in the database.. help please?
https://www.experts-exchange.com/questions/27377089/dynamic-bind-variables.html
I've the procedure:
proc(param1 varchar2, param2 varchar2)
open ref_cursor for
'select * from sometable where param1 like :param1 and param2 in :param2'
using '%'||param1||'%', '('||param2||')'
LIKE operator works fine for param1 but IN operator won't work for param2.. Tried all possible input scenarios..
i'm calling the procedure in sqlplus like this:
exec :v:=proc('something','''id
tried also
proc('something','id1'',''
no errors but always zero records output..but there are matching records in the database.. help please?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.. Missing Right Paranthesis with the code above.. but the parenthesis are correctly matching..
Oops, I forgot the table function
table(str2tbl(:param2))
table(str2tbl(:param2))
You can't do it using bind variables but you can using string concatenation.
create or replace procedure myproc(inVar1 in varchar2, outCur out sys_refcursor)
is
begin
open outCur for
'select ''Hello'' from dual where dummy in (' || inVar1 || ')';
end;
/
show errors
--sqlplus setup to test it
var myCur refcursor
exec myProc('''X'',''Y'',''Z''', :mycur);
print mycur
'select * from sometable where param1 like :param1 and param2 in (select * from table(str2tbl(:param2)))'
using '%'||param1||'%', param2;
but, I have to wonder, why are you using dynamic sql for this? Nothing in your example is dynamic.
for simplicity, I recommend giving your variables and your columns with different names
using '%'||param1||'%', param2;
but, I have to wonder, why are you using dynamic sql for this? Nothing in your example is dynamic.
proc(param1 varchar2, param2 varchar2)
open ref_cursor for
select * from sometable
where sometable.param1 like proc.param1
and sometable.param2 in (select * from table(str2tbl(procparam2)));
for simplicity, I recommend giving your variables and your columns with different names
ASKER
thanks, table(..) worked great..
Just before closing out the question,
slightvv, yes the current implementation we have is using concatenation.. but it's introducing sql injection vulnerablity which is why we're changing this to dynamic binding.. any other ideas / suggestions?
Just before closing out the question,
slightvv, yes the current implementation we have is using concatenation.. but it's introducing sql injection vulnerablity which is why we're changing this to dynamic binding.. any other ideas / suggestions?
If your app will allow injected IN values then no, I cannot think of a way around it off the top of my head.
Can you not check for syntax to see 'extra' values injected?
Can you not check for syntax to see 'extra' values injected?
using '%'||param1||'%', param2