Re: ROW_Number problem

In article <f724a6c4.0208220427.3ed4be96_at_posting.google.com>, c266366430_at_hotmail.com says...
>
>Dear folks,
>
>I am really in pain because of this question:
>Can I use ROW_NUMBER() is Oracle Stored Procedure?
>

read

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3027089372477

>We use Oracle 8i at work, and I find it horrifying that even though I
>can run this code under SQl*Plus without problem:
>
> select
> ROW_NUMBER() OVER (ORDER BY sec_no, net_no) counta,
>
> testa.* from testa
>
>Yet when I put it in a Stored Procedure:
>
>----------------------------------------------------------------
>
>CREATE OR REPLACE PACKAGE DeeCay_Test_Package AS
> TYPE refcur IS REF CURSOR;
> PROCEDURE DeeCay_Test
> (
> P_cursor OUT refcur
> );
>END DeeCay_Test_Package;
>/
>
>CREATE OR REPLACE PACKAGE BODY DeeCay_Test_Package AS
> PROCEDURE DeeCay_Test
> (
> P_cursor OUT refcur
> )
> IS
> BEGIN
> OPEN p_cursor FOR
> select
> ROW_NUMBER() OVER (ORDER BY sec_no, net_no) counta,
> testa.* from testa;
> END DeeCay_Test;
>END DeeCay_Test_Package ;
>/
>
>----------------------------------------------------------------
>
> it failed miserably.
>
>----------------------------------------------------------------
>
>SQL> show error
>Errors for PACKAGE BODY DEECAY_TEST_PACKAGE:
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>10/21 PLS-00103: Encountered the symbol "(" when expecting one of
>the
> following:
> , from into bulk
>
>----------------------------------------------------------------
>
> After I remark out the ROW_NUMBER(), the error message
>disappear.
> I wrote a very long query heavily rely on ROW_NUMBER(), and it
>works great under SQl*Plus. Is there any workaround to make
>ROW_NUMBER() working under Store Procedure?
>
> Thanks in advance.
>
>Deecay One

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp