Paging through a query result
The following query gets all of the rows between LowerBound and HigherBound from the ordered result set in the innermost query. This only works with Oracle8i, release 8.1 and up (order by in sub-queries not recognized until then). I used the data dictionary table all_objects as an example, you can put your own query there.
SELECT *
FROM ( SELECT a.*, ROWNUM r
FROM ( SELECT *
FROM all_objects ao
WHERE ao.object_type = 'PACKAGE'
ORDER BY ao.object_name ) a
WHERE ROWNUM <= :HigerBound )
WHERE r >= :LowerBound
Then you may say how about this query:
SELECT *
FROM (SELECT ao.*, ROWNUM r
FROM all_objects ao
WHERE ao.object_type = 'PACKAGE'
ORDER BY ao.object_name ) a
WHERE a.r between :LowerBound and :HigerBound
Well, that works too, but may not be as efficient as the first one. For more detailed discussion, read here and here.
Related articles:
- ORDER BY no order
- Query returning individual chars
- An Easy Way to Convert a Hierarchical Query Result to XML
nice query, helpful and time saving.
We found that style query to be so useful that we ended up creating a function that will wrap any SELECT statement stated a string, and return the result as an open cursor, with an OUT parameter that contains the total count that would be returned from the original cursor.