I would like to limit query results in oracle to support pagination.

In postgres, mysql, and sqlite you can use the LIMIT and OFFSET modifier, e.g.,

mysql> SELECT login FROM users ORDER BY login LIMIT 2 OFFSET 70001;
+-------------+
| login       |
+-------------+
| acrobat11   | 
| acrobat12   | 
+-------------+
2 rows in set (0.03 sec)

mysql>

In mysql and sqlite you can use a two-parameter version of LIMIT, and the format is

LIMIT offset, count

Which is identical to

LIMIT count OFFSET offset

In oracle you can leverage the magic ROWNUM variable, although the value of ROWNUM is assigned before an ORDER BY is applied (so the ORDER BY sql must be nested). There is a lengthy and very informative Ask Tom article that covers ROWNUM as well as optimization information.

For reference, to use ROWNUM as a LIMIT expression the general form is,

SELECT * FROM (...) WHERE ROWNUM <= 10

And a pagination query using ROWNUM as both LIMIT and OFFSET is,

SELECT * FROM
  ( SELECT /*+ FIRST_ROWS(10) */  a.*, ROWNUM rnum
    FROM ( ... ) a
    WHERE ROWNUM < 20 )
WHERE rnum >= 10;

The oracle equivalent of the above mysql would be as follows,

foo_owner@FOO> SELECT login FROM
 2  ( SELECT /*+ FIRST_ROWS(2) */  a.login, ROWNUM rnum
 3    FROM ( SELECT login FROM users ORDER BY login ) a
 4    WHERE ROWNUM <= 70002 )
 5  WHERE rnum >= 70001;

LOGIN
----------------------------------------------------------------
acrobat11
acrobat12

2 rows selected.

foo_owner@FOO>