Alternatives to LIMIT and OFFSET for paging in Oracle

You will need to use the rownum pseudocolumn to limit results. See here:

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html


Since you're on 10g, you should be able to simplify the ROWNUM approach using analytic functions

SELECT fieldA, 
       fieldB
  FROM (SELECT fieldA,
               fieldB,
               row_number() over (order by fieldA) rnk
          FROM table_name)
 WHERE rnk BETWEEN 5 AND 14;

As of oracle 12c, you could use the top N queries.

SELECT fieldA,fieldB 
FROM table 
ORDER BY fieldA 
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY;

http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php