How to use an array in the IN clause?
Date: 19 Mar 2004 01:33:27 -0800
Message-ID: <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.
I am totally stumped - any ideas out there??
Thanks very much for any responses.
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.
- Danny.