Re: Paging Next/Prev for a result set in Oracle

In article <61bdfd1.0204051722.e691d04_at_posting.google.com>, aabramen_at_cisco.com says...
>
>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"
>

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:127412348064

for the syntax to do this in oracle using rownum.

>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();

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp