We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.
We build implement paging by putting the original query inside a SELECT that fetches only certain rownums from given query, like this:
SELECT * FROM ( SELECT ROWNUM rnum, d.* FROM ( ' || pSql || ' [[filter]] [[sort]] ) d WHERE rownum < [[end]]) WHERE rnum> [[start]]
The only way to implement page counting I could think of was to do a count of all records in pSql. This, however, turned out to be very resource heavy and would needed to be done quite often, as we ofer filtering of data, which in return creates a whole new query in pSql.
Is there a more efficient way of doing a record count?