Re: IN() Operator with Host Array

In article <948f0720.0208140638.60bde782_at_posting.google.com>, chris_doran_at_postmaster.co.uk says...
>
>Is it possible to use a host array to give the set for the IN()
>operator from Pro*C? It works with OCI, but with Pro*C it only seems
>to use the first element of the array. I can't decide from wording of
>the the manual whether it's allowed in Pro*C or not.
>
>I have:
>
>EXEC SQL BEGIN DECLARE SECTION;
>static int Channels[10];
>EXEC SQL END DECLARE SECTION;
>
>EXEC SQL DECLARE ListCursor CURSOR FOR
> SELECT MyItem FROM MyTable WHERE Channel IN(:Channels);
>
>Channels[0] = etc.;
>
>EXEC SQL OPEN ListCursor;
>
>for (;;)
>{
> EXEC SQL Fetch ListCursor INTO :MyItem;
> ...
>}
>
>A look at the C code produced doesn't seem to show any point where the
>size of the Channels array is passed, which is probably why it's
>treated as a set of one.
>
>As would be expected, it works only if I make the IN clause
>IN(:Channels[0],:Channels[1],...). I tried making Channels a string,
>which works with just one item but it fails "invalid number" if the
>string contains a comma-separated list.
>
>Also, the size of my set is variable. I would like to do IN(:Channels
>FOR :COUNT), but this gives a syntax error.
>
>My workaround is to do it by dynamic SQL, but I can't help feeling you
>should be able to pass the set as an array.
>
>Chris

You cannot -- host arrays do one thing -- they execute the same statement over and over and over (n times, once for each array element) on the server without incurring a round trip.

Host arrays are useful for inserts (insert 100 rows in one call) updates -- update 100 rows in one call and deletes -- delete 100 rows in one call.

They are not useful for selects.

Common solution:

create global temporary table in_list( x varchar2(4000) ) on commit delete rows;

once and then

   exec sql insert into in_list values ( :host_array );

   exec sql declare c cursor for

            select * from t where x in ( select x from in_list );

using to_number/to_date on X as needed (or having three columns in there one for dates, numbers and strings.

--
Thomas Kyte (tkyte_at_oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp