Re: Topn query doesnt work in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 29 Dec 1999 11:45:16 -0500
Message-ID: <t5ek6s07j6saepg3jgs51nm7quufi2idn1@4ax.com>

A copy of this was sent to "Rob Edgar" <rob_at_dotmedia.net> (if that email address didn't require changing) On Thu, 30 Dec 1999 00:03:39 +0800, you wrote:

>The query below will execute in SQL Plus but in a PL/SL procedure it wont
>compile giving an error on the order by clause.
>
>select ROWNUM AS Rank, Name, Region, Sales from
> (select Name, Region, sum(Sales) AS Sales
> from Sales GROUP BY Name, Region
> order by sum(Sales) DESC)
>WHERE ROWNUM <= 10
>
>Have I done something wrong or is this a bug, if so is there a workaround.
>
>Rob
>

this only applies to 8i release 8.1 as order by in a subquery is new with that release....

PLSQL doesn't recognize the order by in a subquery (occasionally, the SQL plsql understands is 1 dot release behind, this is one of those cases)...

The workaround is:

scott_at_ORA8IDEV.WORLD> declare

  2      type    rc is ref cursor;
  3      l_ename        varchar2(25);
  4      l_cursor    rc;
  5  begin
  6      open l_cursor for 'select * from ( select ename from emp order by ename

) where rownum < 5';
  7

  8      loop
  9          fetch l_cursor into l_ename;
 10          exit when l_cursor%notfound;
 11          dbms_output.put_line( l_ename );
 12      end loop;
 13  
 14      close l_cursor;

 15 end;
 16 /
ADAMS
ALLEN
BLAKE
CLARK PL/SQL procedure successfully completed.

if you have a large select list, you can use a 'template' cursor to base a record on and fetch into the record. For example, below, i define C1 just like the query I really want run but I leave out the part plsql does not yet understand (just cut and paste the 'real' query into a cursor and leave the order by out -- thats why I select * from ( select * .... ) -- i just got rid of the order by is all)....

scott_at_ORA8IDEV.WORLD> declare
  2 type rc is ref cursor;
  3

  4      l_cursor    rc;
  5      cursor c1 is select * from ( select * from emp ) where rownum < 5;
  6  
  7      l_rec  c1%rowtype;
  8  begin
  9      open l_cursor for 'select * from ( select * from emp order by ename )

where rownum < 5';
 10

 11      loop
 12          fetch l_cursor into l_rec;
 13          exit when l_cursor%notfound;
 14          dbms_output.put_line( l_rec.ename || ' ' || l_rec.empno );
 15      end loop;
 16  
 17      close l_cursor;

 18 end;
 19 /
ADAMS 7876
ALLEN 7499
BLAKE 7698
CLARK 7782 PL/SQL procedure successfully completed.

--
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 Wed Dec 29 1999 - 10:45:16 CST