Data Paging With Oracle PL/SQL Web Toolkit

Hello all,

I'm working with Oracle 9i and the Web Toolkit, and one of our requirements for the search page is that the results are limited by a software parameter (45) for every page. And the end of the results, there is a list of hotlinks to the pages in the result set. Anyone who's used .NET is probably familiar with the Datagrid control, and how it can be made to be page-able. We are hoping to accomplish functionality similar to that of a .NET datagrid.

Currently, the way we do this is very, very ugly and goes something like this:

1. build the search query from the user criteria.
2. open a reference curso with the search query
3. loop through the query and count up all the records
4. close the cursor
5. take the total and calculate the # of pages for data limiting
6. Open the cursor again (with the same query)
7. loop through display the first 45 (or whatever the parameter is) results
8. Print links to other pages

After discussing the search page(s) with my supervisor, we've decided to clean them up. Since the interface code and the program logic are all intermingled throughout the page, this makes the clean up interesting.

I feel confident that some of my initial thoughts on clean up are pretty solid.
My approach will be to encapsulate program logic into its own functions where ever possible, as the 3 search pages will now share most of their functionality.

But I'm still unsure about making our data pageable, or as we call it, Data Limiting. Ideally, since this functionality is used throughout the application, and its a copy-paste-job everywhere, I'd like to make the function general enough to work in various applications. I'm also confident that someone has solved this problem before. I think using some parameter based off the page number, and using that to calculate the lower and upper bound for a ROWNUM clause in the SQL statement would work. Does anyone have any thoughts?

Has anyone done this before and be willing to share what worked for them? Received on Mon Sep 26 2005 - 11:06:48 CDT