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 !




> > 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).