oracle aide

August 17, 2007

Low cost pagination query

Filed under: Apex — oracleaide @ 9:01 pm

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.*,
        case
          when min(r_num) over () = 1 then 1
          else 0
        end first_page,
        case
          when max(r_num) over () = r_max then 1
          else 0
        end last_page
      from (
        select empno, ename, mgr, hiredate, sal,
            comm, deptno, rownum r_num,
            max(rownum) over () r_max
          from scott.emp
          where rownum <= :row_max_next order by empno) a
          where r_num between :row_min and :row_max

    Where:

  • 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.

    Very frugal.

    The FIRST_PAGE and the LAST_PAGE columns tell the style sheet when to hide the NEXT and the PREVIOUS links.

    To be continued…

    Advertisements

    Leave a Comment »

    No comments yet.

    RSS feed for comments on this post. TrackBack URI

    Leave a Reply

    Please log in using one of these methods to post your comment:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Blog at WordPress.com.

    %d bloggers like this: