Re: LIMITing output

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Feb 2000 13:33:13 -0500
Message-ID: <itu2bssah5kp22jivd92fgofev94s0vqvm@4ax.com>

A copy of this was sent to kev <kevin.porter_at_fast.no> (if that email address didn't require changing) On Mon, 21 Feb 2000 16:59:27 +0000, you wrote:

>Hi,
>
>I've been trying to find out for a while how to select the Nth M rows
>from a resultset, for the purpose of paginating results on a web page,
>eg show news stories 1-10 on the first page, user clicks 'More', gets
>news stories 11-20, etc.
>
>I never found a way to do it in Oracle 8.0.5 (mainly because of
>ORDER BY clauses not being allowed in subqueries) but I was told that 8i
>would be better for this because it does allow ORDER BY clauses in
>subqueries. I've upgraded to 8i now (on Red Hat 6.1) and a quick test
>failed to show that ORDER BY clauses in subqueries are indeed supported.
>
>How do you people usually tackle the problem of paginating results on
>web pages? Does 8i indeed support ORDER BY clauses in subqueries?
>

8i does but the plsql parser doesn't recognize this syntax yet in 8.1.5.

Here is a sample showing it working in SQL, not working in PLSQL and how to get it to work in PLSQL:

ops$tkyte_at_8i> variable N number
ops$tkyte_at_8i> variable M number
ops$tkyte_at_8i> 
ops$tkyte_at_8i> exec :N := 5

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> exec :M := 10

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select ename, sal from emp order by sal   2 /

ENAME SAL
---------- ----------

SMITH             800
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000

14 rows selected.

ops$tkyte_at_8i> select *
  2 from ( select a.*, rownum rnum

  3             from ( select ename, sal from emp order by sal ) a
  4            where rownum <= :M )

  5 where rnum >= :N
  6 /

ENAME SAL RNUM
---------- ---------- ----------

MARTIN           1250          5
MILLER           1300          6
TURNER           1500          7
ALLEN            1600          8
CLARK            2450          9
BLAKE            2850         10

6 rows selected.

ops$tkyte_at_8i> 
ops$tkyte_at_8i> 
ops$tkyte_at_8i> declare

  2 cursor c1( n in number, m in number ) is   3 select *
  4 from ( select a.*, rownum rnum

  5             from ( select ename, sal from emp order by sal ) a
  6            where rownum <= M )

  7 where rnum >= N;
  8 begin

  9          for x in c1(n,m) loop
 10                  null;
 11          end loop;

 12 end;
 13 /

           from ( select ename, sal from emp order by sal ) a
                                             *

ERROR at line 5:
ORA-06550: line 5, column 46:
PLS-00103: Encountered the symbol "ORDER" when expecting one of the following: . ) , @ with <an identifier>
<a double-quoted delimited-identifier> group having intersect minus partition start union where connect The symbol "group was inserted before "ORDER" to continue.

ops$tkyte_at_8i> 
ops$tkyte_at_8i> 
ops$tkyte_at_8i> declare
  2          type rc is ref cursor;
  3          c1      rc;
  4          l_ename varchar2(25);
  5          l_sal   number;
  6          l_rnum  number;
  7  begin
  8          open c1 for 'select *
  9                     from ( select a.*, rownum rnum
 10                              from ( select ename, sal from emp order by sal
) a
 11                             where rownum <= :M )
 12                    where rnum >= :N' using 10, 5;
 13          loop
 14                  fetch c1 into l_ename, l_sal, l_rnum;
 15                  exit when c1%notfound;
 16                  dbms_output.put_line( l_ename );
 17          end loop;
 18          close c1;

 19 end;
 20 /
MARTIN
MILLER
TURNER
ALLEN
CLARK
BLAKE PL/SQL procedure successfully completed.

>TIA,
>
>- Kev

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 21 2000 - 12:33:13 CST