Passing in Comma Separated List to Stored Proc Input Param

[WilliamR]

Also see here.

If PL/SQL could parse comma-separated lists transparently like that, how would it know when not to? What happens when you pass an address, say, that happens to contain a comma?

You could try something similar to this (requires 9i):

var i_list VARCHAR2(100)
exec :i_list := '7369,7566,7788,9999,5'

SELECT empno, ename FROM emp
WHERE  empno IN
       ( SELECT EXTRACTVALUE(xt.column_value,'e')
         FROM   TABLE(XMLSEQUENCE
                ( EXTRACT
                  ( XMLTYPE('<coll><e>' ||
		            REPLACE(:i_list,',','</e><e>') ||
			    '</e></coll>')
                  , '/coll/e') )) xt );

or as a PL/SQL block,

DECLARE
    p_csvlist VARCHAR2(100) := '7369,7566,7788,9999,5';
    p_xmllist XMLTYPE :=
        XMLTYPE('<coll><e>' || REPLACE(p_csvlist,',','</e><e>') || '</e></coll>');
BEGIN
    FOR r IN (
        SELECT empno, ename FROM emp
        WHERE  empno IN
               ( SELECT EXTRACTVALUE(xt.column_value,'e')
                 FROM   TABLE(XMLSEQUENCE(EXTRACT(p_xmllist,'coll/e'))) xt )
        )
    LOOP
        DBMS_OUTPUT.PUT_LINE(r.empno || ' ' || r.ename);
    END LOOP;
END;
/