Display only part of resultset

From: Pavel Vetesnik <Pavel.Vet_at_volny.cz>
Date: Fri, 19 Dec 2003 16:42:17 +0100
Message-ID: <brv66g$2jnr$1@ns.felk.cvut.cz>

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 Received on Fri Dec 19 2003 - 09:42:17 CST