I am able to limit resultset in pagination using mysql but in oracle rownum is not working as expected.
SELECT * FROM TABLENAME WHERE TYPE=1 LIMIT 0,5
SELECT * FROM TABLENAME WHERE TYPE=1 LIMIT 5,10
SELECT * FROM TABLENAME WHERE TYPE=1 LIMIT 10,15
For Oracle (Not working)
SELECT * FROM TABLENAME WHERE TYPE=1 AND ROWNUM > 0 AND ROWNUM < 5
SELECT * FROM TABLENAME WHERE TYPE=1 AND ROWNUM > 5 AND ROWNUM < 10
SELECT * FROM TABLENAME WHERE TYPE=1 AND ROWNUM > 10 AND ROWNUM < 15
It's more complicated in Oracle, actually. Rownums are assigned only to the rows that are selected by the WHERE clause, always starting at 1. So, for example, the following query will never return any row, regardless of how many rows are there in the table:
At the same time, if no ORDER BY is given, the order of rows returned by the database may differ from call to call, and successive pagination queries might skip some rows or return some rows twice (or more times) as a result. In pagination queries, always use ORDER BY!
To sum it up: for proper pagination in Oracle, the rownums have to be generated by an inner query and then limited by the outer query:
A slightly less convoluted is pagination using ROW_NUMBER analytic function. Unlike the ROWNUM, this function always assigns numbers, even if the rows are not selected by a WHERE clause. Unfortunately, analytic functions cannot be directly used in where clauses, so an inner query is still required:
In real-world code, you'd use parameters instead of hard-coded numbers, of course (see PreparedStatement)!