Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fwd: Re: How to select and display 10 records at a time from a table
>
> Does anybody know how to do it better way?
>
You can do it (under SQL*Plus) by running
@next10ini
then
@next10
and then
/
repetitively, where next10ini.sql is :
-- -- next10ini.sql -- -- To be called ONCE before calling repetitively next10.sql -- (to be called each time you want to reset everything) -- -- If there is an ORDER BY then you must -- store new_value for each column in the -- ORDER BY. -- If there is none, use this : -- -- column rwid noprint new_value last_row -- -- and write the query in next10.sql as: -- -- select * -- from (select rowid rwid, table_name -- from dictionary -- where rowid > '&&last_row') -- where rownum < 11; -- -- (note that it would not work with this example because ROWIDs -- cannot be used with the DICTIONARY view) -- column table_name new_value last_row -- -- Initialize with a value which is smaller -- than the smallest value you want to return -- define last_row = '0' set verify off and next10.sql is : -- -- next10.sql -- -- To be called repetitively. -- select * from (select table_name from dictionary where table_name > '&&last_row' order by table_name) where rownum < 11 / but it is not madly efficient. The only proper way is to open a cursor and fetch by batches of 10 rows - or what has been suggested, setting the pagesize to 10 and pause !Received on Wed Aug 29 2001 - 07:54:27 CDT
> > But this will return only the first ten rows not subsequent sets. How do I
> > display rows from 11 to 20, 21 to 30 and so on?
>
> ROWNUM is counting output tuples. You cannot use ROWNUM > 10.
>
> I think you have to order select by your primary key, make filter based on
> your primary key and take only first 10 tuples using ROWNUM.
>
> JP
-- Regards, Stephane Faroult email: sfaroult_at_oriole.com Oriole Corporation Voice: +44 (0) 7050-696-269 Fax: +44 (0) 7050-696-449 Performance Tools & Free Scripts -------------------------------------------------------------- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -------------------------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
![]() |
![]() |