Re: How to use an array in the IN clause?

"Daniel Kunz" <dkunz_at_europe.com> wrote in message news:614e041b.0403190133.6996da81_at_posting.google.com...
| I'm using Oracle 9.2 and I would like to use an array/collection in
| the IN clause of a SELECT statement. My application is programmed in C
| and I'm using OCI. The select statement I'm using looks like this:
| "SELECT value FROM numbertab WHERE zvalue IN :1"
| The number table is rather huge (it contains several millions of
| rows). The ':1' parameter corresponds to a set containing about 300
| numbers (the numbers of this input set are calculated in realtime).
| Because issuing 300 select statements separately would cost lots of
| performance, I came up with the idea of using an array/collection as
| input for the select statement.
| Unfortunately, I couldn't find any OCI documentation how to do it. Is
| it possible at all? I guess that OCIBindArrayOfStruct() cannot be
| used, because it doesn't allow to specify the size of the array.
|
| I am totally stumped - any ideas out there??
|
| Thanks very much for any responses.
|
| - Danny.

you need a pipeline function -- see my post 'example: pipelined function', and/or search for PIPELINED at http://asktom.oracle.com (or TABLE CAST)

;-{ mcs Received on Sat Mar 20 2004 - 02:50:43 CET