>
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
