Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fwd: Re: How to select and display 10 records at a time from a table

RE: Fwd: Re: How to select and display 10 records at a time from a table

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 29 Aug 2001 05:54:27 -0700
Message-ID: <F001.0037AF1F.20010829060637@fatcity.com>

>
> 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 !



> > 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).
Received on Wed Aug 29 2001 - 07:54:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US