PL/SQL: How pass vals list for "select where in ()"

[Jon Doe]

Hi,
I think what I'm trying to do is simple. All I want to do is to be able to
pass a list of values as a parameter into my stored procedure so that I can
do something like:

select * from emp where name in (paramVar)
- in place of -
select * from emp where name in ('a','b','c')

Needless to say, the whole point of this is that list size will be dynamic
and unknown before the proc is called. Passing a simple varchar2 of comma
delimited values of course does not work.

Any idea?
Thanks!

[Sylvain Brun]

two silly ugly, higher-cost (?) indirect ways :

1) passing through a temporary created table containing your list of values
:

create table TMP (NAME ...)
insert into TMP values (...)

select * from emp where name in (SELECT NAME from TMP)

2) a better way : unsing LIKE instead of IN :

select * from emp where ','||ParamVar||',' like '%,'||name||',%'

--
=================================
Sylvain Brun -- Société INFOLOGIC
- Services et conseils en Informatique -
Tél. 04 75 82 16 40 - Fax. 04 75 82 16 38
=================================

Jon Doe <Fake.A...@Fake.com> a écrit dans le message :
NERa6.4635$165.3...@newsread2.prod.itd.earthlink.net...

[bil...@my-deja.com]

>I want to...pass a list of values as a parameter into my stored

>procedure so that I can do something like:
> select * from emp where name in (paramVar)

OK. This code isn't terribly efficient, but it works. It uses Oracles
user-defined types and casting to allow sub-select from a comma
delimited list.
select * from experts where
expert_item_enum IN
SELECT * FROM
THE
( SELECT cast( Pkg_Util.In_List
(question_area_enum_list_in)
AS codetableType )
FROM dual ) x)

where "pkg_util.in_list" takes a comma-delimited list and returns it as
a an array usable in an sql query (using the "THE" predicate )

FUNCTION In_List( p_string IN VARCHAR2 ) RETURN codeTableType
IS
l_data codeTableType := codeTableType();
l_string LONG DEFAULT p_string || ',';
l_n NUMBER;
BEGIN
LOOP
EXIT WHEN l_string IS NULL;
l_data.extend;
l_n := INSTR( l_string, ',' );
l_data( l_data.COUNT ) := SUBSTR( l_string, 1, l_n-
1 );
l_string := SUBSTR( l_string, l_n+1 );
END LOOP;
RETURN l_data;
END;

here's the definition for codeTableType:

create TYPE "CODETABLETYPE" as table of varchar(20);

Again, I don't realy know how efficient this is when compared with
storing results in temporary tables. I welcome anyone's comments.

I can email you better versions of the source code if you like.

Sent via Deja.com
http://www.deja.com/