Re: Data Paging With Oracle PL/SQL Web Toolkit

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 26 Sep 2005 12:38:12 -0700
Message-ID: <1127763492.164696.317160@g47g2000cwa.googlegroups.com>

redrobot5050_at_gmail.com wrote:
> 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?

The best way I have found to 'page' through results is using a query like this (taken directly from asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:12226081439452996890::NO::F4950_P8_DISPLAYID,F4950_P8_B:127412348064,Y)

select *
  from ( select a.*, rownum rnum

           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )

 where rnum >= MIN_ROWS
/

For example:-

select rn, object_name, object_id
from
(
select *
from
(
  select object_id, object_name, rownum rn   from all_objects
  order by object_name desc
) where rn <= 45
)
where rn >= 10
order by rn asc

The only way I have found to get the total number of records is to run effectively the same query twice, once selecting only count(*) without the limiting part and the other time selecting the actual columns.

To page through the results, simply pass in the page number from the webpage and use it as a multiplier in your max and min eg

records_per_page = 45

max_record = ( page_num - 1 )*records_per_page + 1

min_record = ( page_num )*records_per_page

Stephen Received on Mon Sep 26 2005 - 14:38:12 CDT