Using Select .. IN with a IN Parm

#1

How can I use a varchar2 IN parameter in an IN clause in PLSQL??

i.e.
v_parms IN varchar2 (v_parms would look like
('scott','dave','mike') )

Begin

Select * from table
where table_field in v_parms;

End

Do I have to use a DBMS_SQL.Open .. DBMS_SQL.Parse sequence??

Thanks
SW

#2

"Scott" <scott.wilson19 (AT) comcast (DOT) net> wrote

Quote:

How can I use a varchar2 IN parameter in an IN clause in PLSQL??

i.e.
v_parms IN varchar2 (v_parms would look like
('scott','dave','mike') )

Begin

Select * from table
where table_field in v_parms;

End

Do I have to use a DBMS_SQL.Open .. DBMS_SQL.Parse sequence??

Thanks
SW

No, you need to write a function that converts the comma delimited list to a

pl/sql table and do a cast to table. see asktom.oracle.com
Jim

#3

Thanks Jim,

I will research CAST and see what it can do.

Much Appreciated.
SW

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> wrote

Quote:

"Scott" <scott.wilson19 (AT) comcast (DOT) net> wrote in message
news:6ZadnbFGANl7AoffRVn-ig (AT) comcast (DOT) com...
How can I use a varchar2 IN parameter in an IN clause in PLSQL??

i.e.
v_parms IN varchar2 (v_parms would look like
('scott','dave','mike') )

Begin

Select * from table
where table_field in v_parms;

End

Do I have to use a DBMS_SQL.Open .. DBMS_SQL.Parse sequence??

Thanks
SW

No, you need to write a function that converts the comma delimited list to
a
pl/sql table and do a cast to table. see asktom.oracle.com
Jim

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Using Select .. IN with a IN Parm

comp.database.oracle comp.database.oracle

Using Select .. IN with a IN Parm - 02-21-2005 , 08:59 PM

Re: Using Select .. IN with a IN Parm - 02-22-2005 , 09:47 PM

Re: Using Select .. IN with a IN Parm - 02-22-2005 , 10:03 PM