Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Paging Next/Prev for a result set in Oracle
I've searched all over the place and thought about this problem for a
while; however, I don't have a satisfactory answer yet.
The basic issue is as follows: I am getting a large resultset but I am only going to display 10 items from the result set at a time. The user will be clicking page by page to get through the result set.
Here the typical sql statement.
"select webx_id, subject, creation_date from messages where cco_id=?
order by subject"
In mySQL there is a limit clause that one can use, so the statement would look like this
"select webx_id, subject, creation_date from messages where cco_id=?
order by subject limit 400, 10"
This would return 10 items after the first 400. The benefit is that there is a much smaller network transfer between the database and the application server.
Oracle does not support a limit clause (and row number does not work for the above sql statement). However, jdbc 2.0 offers the ability to set a cursor to a specified location and only fetch the results from that location. My QUESTION is: Are those the only results that are transffered over the network, or does the driver still tranfer the whole result set over the network and then iterated over it? Thank you in advance for your help. Here is the sample code:
PreparedStatement lookupMessages; ResultSet rs = null; String sql = "select webx_id, subject, creation_date from messages where cco_id=?"; lookupMessages = dbConnection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); lookupMessages.setString(1, userID); rs = lookupMessages.executeQuery(); for (int resultSetIterator = startingMessageNumber; resultSetIterator < endingMessageNumber; resultSetIterator++) { if (rs.absolute(resultSetIterator)) { String messageID = rs.getString(1); String subject = rs.getString(2); Date datePosted = rs.getDate(3); } } rs.close();Received on Fri Apr 05 2002 - 19:22:08 CST
![]() |
![]() |