From: mcstockX@Xenquery   
      
   "Mark C. Stock"  wrote in message news:...   
   |   
   | "Berend"  wrote in message   
   | news:bdd9ac20.0401271301.22cdb65e@posting.google.com...   
   | | I am trying to pass multi values into a where clause with an in clause   
   | | in a store procedure to use in a Crystal report. This can change   
   | | depending on the user.  Maybe there is another way to pass multi   
   | | values.   
   | |   
   | |   
   | | CREATE OR REPLACE PROCEDURE eva_sp_wrk014_spec_test (   
   | |    p_eva_product_header_ids   IN       VARCHAR2,   
   | |    cur_spec_cd                IN OUT   
   | | sysadm.eva_pkg_wrk014_spec_test.ref_spec_spec_cd   
   | | )   
   | | AS   
   | | BEGIN   
   | |    OPEN cur_spec_cd   
   | |     FOR   
   | |        SELECT *   
   | |          FROM sysadm.eva_product_header eph   
   | |         WHERE eph.eva_product_header_id in (p_eva_product_header_ids);   
   | | END eva_sp_wrk014_spec_test;   
   |   
   | the IN clause requires separate values (i.e. a separate bind variable for   
   | each value), and you've got all your values stuffed into one variable, the   
   | equivalent of   
   |   
   | WHERE eph.eva_product_header_id in ( '00,01,23,43,09,33' )   
   |   
   | which attempts to find the value '00,01,23,43,09,33' not the one of the   
   | individual values   
   |   
   | you'll need to rewrite your proc to use dynamic sql, or pull a trick like   
   |   
   | WHERE instr( p_eva_product_header_ids, cDelimiter ||   
   | eph.eva_product_header_id || cDelimiter) > 0   
   |   
   | (the p_eva_product_header_ids parameter would need to have delimiters   
   | between each value, and at the first and last position) -- not great for   
   | performance if it's the only criteria   
   |   
   | other options: write the list of IDs to a temp table (perhaps within the   
   | proc itself) and use a subquery or join to the temp table   
   |   
   | i think there's also a way these days to write a proc that returns a   
   rowset   
   | that can be used as a SQL table -- that might be another way to transform   
   | the common separated list of ids into something useful in a non-dynamic   
   SQL   
   | statement   
   |   
   | or -- write a bunch of explicit ORs that can handle up to the max number   
   or   
   | IDs you think you'd be searching for, then parse out the IDs into local   
   | variables   
   |   
   | -- mcs   
   |   
   |   
      
   here's a reference to the using the TABLE and CAST operators/keywords to   
   feed a the contents of a multi-valued variable to a SQL statement (thanks to   
   another post by dan morgan) http://www.psoug.org/reference/conditions.html   
      
   (forget about the temp table and explicit ORs suggestions)   
      
   -- mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)