Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL: How pass vals list for "select where in ()"
>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/
Received on Mon Jan 22 2001 - 09:37:09 CST