using IN operator with USING clause

in regards to this:
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_27377089.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','''id1'',''id2''')

tried also
proc('something','id1'',''id2')

no errors but always zero records output..but there are matching records in the database.. help please?


LVL 17
ram_0218Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
you can't bind an "IN" collection like that.

you can use something like str2tbl though...


CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);

            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;

        RETURN;
            WHEN NO_DATA_NEEDED THEN
            null;
    END str2tbl;

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:
elect * from sometable where param1 like :param1 and param2 in (select * from str2tbl(:param2))'
   using '%'||param1||'%', param2
ram_0218Author Commented:
Thanks.. Missing Right Paranthesis with the code above.. but the parenthesis are correctly matching..
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
Oops, I forgot the table function

table(str2tbl(:param2))

slightwv (䄆 Netminder) Commented:
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

Open in new window

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

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)));

Open in new window



for simplicity,  I recommend giving your variables and your columns with different names
ram_0218Author Commented:
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?
slightwv (䄆 Netminder) Commented:
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?
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.