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