ROW_Number problem

From: Daniel Kwan <c266366430_at_hotmail.com>
Date: 22 Aug 2002 05:27:28 -0700
Message-ID: <f724a6c4.0208220427.3ed4be96@posting.google.com>

Dear folks,

I am really in pain because of this question: Can I use ROW_NUMBER() is Oracle Stored Procedure?

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 Received on Thu Aug 22 2002 - 07:27:28 CDT