OCI, Arrays and count(*)

Suppose I am using the OCI array based interface.

I have a whole bunch of values already in an array, and I want to count the number of occurances of each of them in a particular table.

The obvious approach is to formulate a query like

        select count(*) from table where xxx = :1

then bind my array to :1 and execute, fetch and smile. I don't seem to be getting to the smile part, as only one row is ever returned, presumably for the first value in my array.

Does anyone have a way of tricking oracle into calculating count(*) for each value in my array?

On a related matter, has anyone got a technique for selecting a block of values from a sequence? The query

        select my_seq.nextval from dual

will also return only one row at a time, requiring an exfet() to retrieve each and every value.

This last problem is not so urgent as I can fudge the required results by bumping up the increment on the sequence from 1 to <block-size> and filling in the gaps myself...

Thanks

Keith Whitwell
+ 61 3 9865 7112 Received on Tue Aug 15 1995 - 00:00:00 CEST