CallableStatements and Scrolling

[Kevin Conaway]

Hi,

I am using stored procedures with an Oracle database to return cursors. However, the cursors that it returns are always TYPE_FORWARD_ONLY. I am trying to set the type to be TYPE_SCROLL_INSENSTIVE, but it does not seem to be working.

Here is the sample code I am using

long id = 1;
Connection conn = ConnectionManager.getConnection();
CallableStatement stmt = conn.prepareCall( "CALL ? := F_MY_FUNCTION(?)", 
   ResultSet.TYPE_SCROLL_INSENSITIVE, 
   ResultSet.CONCUR_READ_ONLY);
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.setLong(2, id);
stmt.execute();
 
ResultSet rs = (ResultSet) stmt.getObject(1);
 
while ( (rs != null) && rs.next() ) {
...
}
 

In the above example, rs is always TYPE_FORWARD_ONLY. Anyone know how to fix this?

Thanks,

Kevin
[ March 13, 2006: Message edited by: Kevin Conaway ]

[stu derby]

I don't know how to fix this, mostly because using Oracle scrollable ResultSets are implemented by caching the entire ResultSet in Java memory; the ResultSets my application has are often too big for that. Also, last I checked, ResultSet.last() transfered every row of a scrollable result to get to the end.

See:
http://download-east.oracle.com/docs/cd/B19306_01/java.102/b14355/resltset.htm#CIHCHBJB

[Kevin Conaway]

Hi Stu,

Thanks for getting back to me.

How do you handle large result sets with oracle? I was hoping to be able to index or page into the result set to retrieve the items I wanted. I definitely do not want to read every row sequentially to get to where I want.

Thanks,

Kevin

[stu derby]

How do you handle large result sets with oracle?

First, a ResultSet should only contain rows you want; if you only want a page of data, only select a page of data - it's much much faster.
http://asktom.oracle.com/pls/ask/f?p=4950:8:2727786998497022568::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:76812348057
http://asktom.oracle.com/pls/ask/f?p=4950:8:2727786998497022568::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064
http://asktom.oracle.com/pls/ask/f?p=4950:8:2727786998497022568::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:20663081751269

Second, a well-designed web application should be able to handle display of an infinitely large ResultSet; the limit should be the memory of the client's browser and the client's patience, not some arbitrary limit in your code or the memory limit of your JVM. This is accomplished by "streaming" the data through the application; data is read, processed, and output to the response, row by row, with only buffered chunks of the ResultSet and the response in memory at once, not all of either of them. (A few J2EE servers do not normally stream the response, requiring a periodic flush(), but most do).

[Kevin Conaway]

I was hoping to for something less invasive that does not require the modifications of stored procedures.

Also, if I need to know the total number of rows available, is SELECT COUNT(ID) FROM DATA the best way to handle this?

Thanks

[Reid M. Pinchback]

It is the typical way to go, and I think the only way to go in situations where you have any kind of where clause to specify a subset. If you literally want to know the entire table size and don't care about supporting multiple database vendors in your code, there is Oracle metadata that will tell you how many rows there are in the table.

I haven't compared the timings of the two. Conceptually the metadata approach would be constant time and the count approach linear time, but I wouldn't be surprised if you found Oracle query processing spotted such an obvious attempt to measure table size and internally used the metadata anyways.