Re: OCI, Arrays and count(*)

xv02434_at_codac.codac.telecom.com.au (Keith Whitwell) wrote:

>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

Sorry, ARRAYS work only on FETCH for Selects. You can array fetch, but you can't array bind. Array binds only work on Inserts, Updates, and Deletes.

You need to "select count(*), xxx from table where xxx in ( :1, :2, .... :n ) group by xxx" and bind the individual array elements to the :1, ... :n. If you have M array elements and M < N then you would bind NULLS to :m+1, .. :n so you can reuse the statement over and over again.

>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.

Try " select my_seq.nextval from ALL_OBJECTS where rownum <= :n" and set n to the number of sequences you want. As long as N is less then the number of rows in the all_objects table (usually more than 1,000) you will get what you want.

>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...

That is actually the most effcient way to do it.

>Thanks
 

>Keith Whitwell
>+ 61 3 9865 7112

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sat Aug 19 1995 - 00:00:00 CEST