DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Passing in Comma Separated List to Stored Proc Input Param

  1. #1
    Join Date
    Jul 2005
    Posts
    8

    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!!

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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;
    /

  4. #4
    Join Date
    Apr 2009
    Posts
    1
    Hi all,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width