Re: Display only part of resultset

Pavel Vetesnik wrote:

> Greetings,
>
> I have question which was probably discussed million times before, but I was
> not able to find an answer.
> I have little application written in PL/SQL that allows user to search
> database. The result can be several hundreds (or thousands) records.
> I want to show only limited amount of them (let's say 20 records) per page
> and let user to skip to other pages with results. What is the preffered way
> to do this?
>
> (a) The easiest way is to create cursor and within loop fetch & skip to
> particular "page". But I am not sure that fetching several thousands of
> records to find 150th page is good idea.
>
> (b) The other way is using "rownum" column. I can take only first 20 rows
> using such query:
> ------------------------------------------
> SELECT nazev, psh1
> FROM (SELECT nazev, psh1 FROM vw_stk WHERE id_psh1=10 ORDER BY nazev)
> WHERE ROWNUM<=20;
> ------------------------------------------
> The problem is, that I can't take other records (only first ones) using this
> technique (condition WHERE ROWNUM>20 AND ROWNUM<=40 can't work, because
> ROWNUM is just pseudocolumn created dynamically).
>
> Please can you tell me, what is the preffered way to do this? Oracle Portal
> is doing it somehow (ie.
> http://newalex.stk.cz:7777/pls/portal30/!JIRIB.RPT_ISSN_VYBER.show).
>
> Thank you very much in advance,
> Pavel
>
>

You cannot skip to page 150 (each containing 20 entries), because you don't know there are 150 pages unless you read all...

So the answer is to use an inline view, with all records, and do your rownum trick around that - that's how Portal does it.

-- 
Merry Christmas and a Happy New Year,
Frank van Bortel