Does Oracle support Server-Side Scrollable Cursors via JDBC??

Question

Hi all

Currently working in the deployment of an OFBiz based ERP, we've come to the following problem: some of the code of the framework calls the resultSet.last() to know the total rows of the resultset. Using the Oracle JDBC Driver v11 and v10, it tries to cache all of the rows in the client memory, crashing the JVM because it doesn't have enough heap space.

After researching, the problem seems to be that the Oracle JDBC implements the Scrollable Cursor in the client-side, instead of in the server, by the use of a cache. Using the datadirect driver, that issue is solved, but it seems that the call to resultset.last() takes too much to complete, thus the application server aborts the transaction

is there any way to implemente scrollable cursors via jdbc in oracle without resorting to the datadirect driver?

and what is the fastest way to know the length of a given resultSet??

Thanks in advance Ismael

Answer 1

"what is the fastest way to know the length of a given resultSet" The ONLY way to really know is to count them all. You want to know how many 'SMITH's are in the phone book. You count them. If it is a small result set, and quickly arrived at, it is not a problem. EG There won't be many Gandalfs in the phone book, and you probably want to get them all anyway.

If it is a large result set, you might be able to do an estimate, though that's not generally something that SQL is well-designed for.

To avoid caching the entire result set on the client, you can try

select id, count(1) over () n from junk;

Then each row will have an extra column (in this case n) with the count of rows in the result set. But it will still take the same amount of time to arrive at the count, so there's still a strong chance of a timeout.

A compromise is get the first hundred (or thousand) rows, and don't worry about the pagination beyond that.

Answer 2

Hi Ismael,

your proposed "workaround" with count basically doubles the work done by DB server. It must first walk through everything to count number of results and then do the same + return results. Much better is the method mentioned by Gary (count(*) over() - analytics). But even here the whole result set must be created before first output is returned to the client. So it is potentially slow a memory consuming for large outputs.

Best way in my opinion is select only the page you want on the screen (+1 to determine that next one exists) e.g. rows from 21 to 41. And have another button (usecase) to count them all in the (rare) case someone needs it.