Re: Wanted to know some info about top 1 query in cursors

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Fri, 24 Dec 1999 18:19:46 +0200
Message-ID: <38639D22.9EB4B272@0800-einwahl.de>

Hi Sudhakar,

I tried the following:

SQL>
SQL> create table secondaryres (

  2  	     secrescode varchar2 (5)
  3  	     , secresdesc varchar2 (10)

  4 )
  5 /

Table created.

 real: 30
SQL>
SQL> create or replace procedure test is   2 Cursor Cur_SecRes is
  3 select secresdesc,secrescode from
  4 (SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY   5 secrescode asc, SECRESDESC desc)
  6 WHERE ROWNUM=1;
  7 Begin
  8 for x in Cur_SecRes
  9 loop
 10 dbms_output.put_line(x.secrescode || ' ,' ||  11 x.secresdesc);
 12 end loop;
 13 End;
 14 /

Warning: Procedure created with compilation errors.

 real: 110
SQL> show errors
Errors for PROCEDURE TEST:

4/49     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 ")" was substituted for "ORDER" to continue.

5/32     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         , ; for

SQL>
SQL> create or replace view v_secondaryres as   2 select secresdesc,secrescode from
  3 (SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY   4 secrescode asc, SECRESDESC desc)
  5 WHERE ROWNUM=1; View created.

 real: 30
SQL>
SQL> create or replace procedure test is   2 Cursor Cur_SecRes is
  3 select secresdesc,secrescode from
  4 v_secondaryres;
  5 Begin
  6 for x in Cur_SecRes
  7 loop
  8 dbms_output.put_line(x.secrescode || ' ,' ||   9 x.secresdesc);
 10 end loop;
 11 End;
 12 /

Procedure created.

 real: 90
SQL> show errors
No errors.
SQL>
SQL> spool off

So this looks like a bug, not a limitation. Any hints? I ran against this problem as well and I worked around it by using a view.

Martin

sudhakar wrote:
>
> Thanks martin,
> It was mistake from my side. Union all query cursor was
> working fine. In the query below I wrongly mentioned as
> group by instead of order by Group by is working fine. I
> want order by clause.
> Martin if u know then please mail me.
>
> I want to know what are the limitations of cursors in terms
> of sql
> I am getting error when for the following procedures......
> 1.
> create or replace procedure test is
> Cursor Cur_SecRes is
> select secresdesc,secrescode from
> (SELECT SECRESDESC,secrescode FROM SECONDARYRES order BY
> secrescode asc, SECRESDESC desc)
> WHERE ROWNUM=1;
> Begin
> for x in Cur_SecRes
> loop
> dbms_output.put_line(x.secrescode | | ' ,' | |
> x.secresdesc);
> end loop;
> End;
> The above procedure is opens the top q query, which is
> required for imporving performance. The query is running in
> Sql Plus, but if I include the query in PL sql block it is
> giving error.
> Is there any way out for running the above query, If any
> body knows then please let me know at
> sudhakar_kb_at_hotmail.com
>
> overall I need to know the limitations of cursor or pl sql
> block,
>
> Thanks once again,
>
> Regards,
> sudhakar
>
> * Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
Received on Fri Dec 24 1999 - 10:19:46 CST