Re: Using JDBC how can I tell how many rows come back withoof looping though them??

Johnathan Mark Smith (smithj_at_statenislandonline.com) wrote:
: Using JDBC how can I tell how many rows come back withoof looping
: though them??

I've been playing with JDBC of late and here's my 0.02 :

Without doing a SELECT count(*), or something similar, with the same selection criteria, then I don't think that this information is readily available from Oracle.

I say 'something similar' as unless you lock the rows that you will retrieve it is entirely possible that someone else could add or delete some of the rows, which makes your original count a little inaccurate.

You can't use row-level locking, via the 'FOR UPDATE' syntax with a COUNT(*) operation in Oracle, so that forces you to retrieve something from the rows in order to get a 100% accurate count. In which case you might as well just return all of the data and be done with it.

Another solution would be to retrieve the count(*) at the same time as the data - I have seen someone suggest a 'SELECT count(*), col1, ... FROM ...' suggestion, but this doesn't work too well in Oracle, but you could fudge it in some way, such as by using a function call in the SELECT that would execute the same query as the main SELECT, thus returning your row count. The performance is likely to be pretty shocking as the function and hence the count(*) would be fired for every row returned and I'm not convinced that the data couldn;t change under your feet affecting the count(*) value.

Another <grin> solution would be to create a generic procdure/function/package that you pass a SELECT statement too, it would execute the statement via DBMS_SQL and stores the retrieved data in a PL/SQL table and you could then have a function that returns a count of the number of rows stored in the Pl/SQL table and could then return the data to you. Liable to take up wads of SGA though.

IAP

--
In an attempt to reduce junk email I use an invalid 'From' address.
My correct email address can be determined by replacing 'not.valid' with 
'value.net'