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):
Code:
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,
Code:
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;
/