Passing in Comma Separated List to Stored Proc Input Param

I have a Stored Procedure in Oracle that accepts a VARCHAR input parameter - I_LIST IN VARCHAR. The value will be a comma separated list "1,2,3".

The SQL statement in the proc is select * from table where id in (I_LIST);

No results are returned. When I just pass in one value it works fine but more then one and I get no results. Any thoughts? Thanks!!

Hi.

It's using the parameter as a single item, not a list of items. For an explanation of what to do look at this:

http://asktom.oracle.com/pls/ask/f?p...D:110612348061

Cheers

Tim...

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;
/

---

Hi all,

Above Query Work Fine For Number Datatype but how can i supply multiple values having varchar datatype