Re: PL/SQL puzzler

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/28
Message-ID: <34d9a154.30261804@192.86.155.100>#1/1

A copy of this was sent to "Charles A. Christiansen, Jr." <charles_at_iconstructs.com>
(if that email address didn't require changing) On Wed, 28 Jan 1998 14:02:03 -0600, you wrote:

>Hi all,
>
>I'm having a bit of trouble getting an ORDER BY clause into a cursor.
>This is my PL/SQL code:
>
>CURSOR c_ipf IS (SELECT EntryID, OrderNum FROM T1
> WHERE MainID = Main_ID AND ParentID = Parent_ID AND LevelNum =
>Level_Num AND OrderNum >= Order_Num
> ORDER BY OrderNum DESC);
>

It'll work if you code:

    cursor c_ipf( Main_id   in t1.mainid%type,
                  parent_id in t1.parentid%type,
                  order_num in t1.orderNum%type,
                  level_num in t1.levelNum%type ) is
          select entryId, OrderNum
            from T1
           where mainId = Main_id
             and ParentId = Parent_id
             and levelNum = Level_num
             and orderNum >= Order_Num
           order by OrderNum desc;

Loose the parens and add the declare of the inputs to the query...

>What I get from the compiler is:
>
>PLS-00103: Encountered the symbol "ORDER" when expecting one of the
>following: .()*@%& (. . .) the symbol ")" was substituted for "ORDER" to
>continue.
>
>I haven't seen anything that says I can't put an ORDER BY clause into a
>cursor, though I have also never seen it done anywhere (the book I'm
>using to learn doesn't have any examples with ORDER BY clauses).
>
>Any ideas / advice / chastisement? :)
>
>Please respond via e-mail if possible - I don't have regular access to
>this group, but I will check it periodically as well. Thanks!
>
>============================
>Charles A. Christiansen, Jr.
>Programmer / Analyst
>Interactive Constructs, Inc.
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 28 1998 - 00:00:00 CST