respected sir,

plz tell me about the way to use resultset...
Tell me,how to use resultset in jsp and oracle while displaying some report..
that is if the resultset contains 25 records, i need to display the first five in one page and it must contain a link called next to view the rest five records and so on..
the last page must have link called previous.

so tell me ,how can i traverse back and forth in the result set to display the records available in it.

thank u,
s.kokila

Hi,
Holding a result set for a long period of time (when the user browses through some tabular data) might not be recomended because :
* the user might go at lunch and your connection will time out, the result set is not properly closed and the database will have a hanging open cursor. Multiply this by some number of users and you get a 'Maximum open cursors exceeded' from the db.
* the presentation tier has to work with 'low level' APIs like SQL
A solution would be :
* when the first request for tabular data is performed obtain and keeop the list of ids for the requested rows. The list of ids (usually intergers or longs) will take a small amount of memory.
* when the user requests a page of tabular data you read from the database the records having the ids in : ids[x] -- ids[x + PAGE+SIZE]
This works fine but you have to be aware of the following conditions:
* the list of ids is consistent with the state of the application when the user initially performed the search/tabular data request. So the list of ids might become stale. For some of ids in your list the records might even be deleted in the meantime by another user. So you should have a functionality that refreshes the stale data.
Best regards, Mircea

You can use the follwoing query to get the pagination over the result set.


select *
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS

Here MIN_ROWS initially will be 1 and MAX_ROWS will be the page size (ie 50, 100). When user clicks on next page your MIN_ROWS = MAX_ROWS + 1, and MAX_ROWS will be (Page Number * 50) so on....

A better solution is to use a scrollable result set.

http://javaalmanac.com/egs/java.sql/CanSrs.html?l=rel