Re: Dynamic view with ORDER BY clause

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Nov 1999 09:38:58 -0500
Message-ID: <WtQqOAvE96pyLpppOuL7bkMmGWLx_at_4ax.com>

A copy of this was sent to "Nick Willemse" <nwillemse_at_earthlink.net> (if that email address didn't require changing) On Wed, 10 Nov 1999 16:39:58 -0600, you wrote:

>Hi everyone,
>
>I have something gor you guys to think about and it goes something like
>this: I am selecting from a dynamic view cause I want to select only the
>first few rows, but I want to first sort it before select the first few
>rows. Now my SQL looks like this:
>
> SELECT * FROM
> (SELECT FirstName,
> LastName
> FROM Person
> WHERE MembershipLevel IN (2,3,4)
> AND FirstNameLower LIKE LOWER(vFirstName)||'%'
> AND LastNameLower LIKE LOWER(vLastName)||'%'
> ORDER BY LastName, FirstName)
> WHERE ROWNUM < 501;
>
>Now here is the problem. This statement works 100% running from SQL/Plus,
>but as soon as I try to put the code in a package, it gives a compilation
>error at the ORDER BY line and doesn't compile at all. Now my solution was
>to change the ORDER BY to a GROUP BY, but still I am unease at why it works
>in SQL/Plus but not in PL/SQL??? BTW I am using Oracle 8i on a Sun Server.
>

Pl/SQL doesn't have 100% of the sql in it as yet (some of the new sql features added for 8.1 didn't make it into the plsql sql parser.

You can code the above like such:

declare
  l_query varchar2(1024) :=
  'SELECT * FROM

      (SELECT FirstName,
                    LastName
         FROM Person
      WHERE MembershipLevel IN (2,3,4)
          AND FirstNameLower LIKE LOWER(:fname)||''%''
          AND LastNameLower LIKE LOWER(:lname)||''%''
     ORDER BY LastName, FirstName)

 WHERE ROWNUM < 501';  

   type rc is ref cursor;
   l_cursor rc;
begin

   open l_cursor for l_query using vFirstName, vLastName;    loop

      fetch l_cursor into ....;
      exit when l_cursor%notfound;
      ...

   end loop;
   close l_cursor;
end;
/

>Thank you in advance for any input.
> Nick
>
>

-- 
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 Thu Nov 11 1999 - 15:38:58 CET