Pagination SQL query for Oracle database

Pagination is a process of dividing information into pages, it's daily task for web developer independently of used technology. Pagination is often described with respect to LAMP technologies (PHP and MySQL sites), for Oracle database it is not very different, but we must write other SQL query because Oracle don't support LIMIT and OFFSET parameters, actual for MySQL. In case of MySQL pagination query is:

SELECT id, name FROM users ORDER BY name LIMIT start, limit

This query for Oracle is shown below:

SELECT *
FROM
  (SELECT a.*,
    rownum AS rn
  FROM
    (SELECT id, name FROM users ORDER BY name
    ) a
  WHERE rownum < (start+limit)+1
  )
WHERE rn >= start+1;

Rownum is number of current row in sampling, note that it starts with 1. ORDER is necessary parameter, otherwise rownum will not have sence.

If input data is page number and page size, Oracle query is:

SELECT * FROM
(
    SELECT a.*, rownum rn
    FROM
    (
        SELECT id, name from users ORDER BY name
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE rn >= (((pageNumber-1) * pageSize) + 1)