Low cost pagination query
Here is a little entertaining project I dealt with lately – to write a generic table browser using Application Express.
Since the browser is generic, i.e. SELECT statement is not known until run time – I cannot use most of the RAD gadgets of the IDE and many useful APEX features, like the built-in pagination.
The solution is simple:
use Dynamic SQL,
select one page of rows at a time,
convert it into XML,
run through a style sheet using DBMS_XMLQuery and
get a complete HTML page at the end.
Handmade pagination is trivial – unless you select from a very large table.
Here is an example of SQL I came up with:
select /*+ first_rows(16) */ a.*,
when min(r_num) over () = 1 then 1
when max(r_num) over () = r_max then 1
select empno, ename, mgr, hiredate, sal,
comm, deptno, rownum r_num,
max(rownum) over () r_max
where rownum <= :row_max_next order by empno) a
where r_num between :row_min and :row_max
row_min – the first row of a page;
row_max – the last row of the page;
row_max_next – the first row of the next page, if it exists.
Since I have the doubtful privilege of building this statement at runtime, I can set the ROWS_FIRST hint to the size of the page plus one. Thus, the query will try to get only a page of rows plus one more row.
The FIRST_PAGE and the LAST_PAGE columns tell the style sheet when to hide the NEXT and the PREVIOUS links.
To be continued…
No comments yet.