Re: first N rows with a cursor

The problem is that PL/SQL is behind a bit in the version.

Use dynamic SQL because it uses the SQL engine, not the PL/SQL engine to parse the syntax.

Martin

Jeff Greif wrote:
>
> Mervyn at ifwdc.com <merv_at_ifwdc.com> wrote in message
> news:8ff7pn$1lg3$1_at_brimstone.medianet.ie...
> > I don't know about the incompatibility, but you can easily achieve the
 same
> > 10 rows in PL*SQL by declaring your cursor as the inner query only, and
 then
> > loop 10 times around the fetch. That gives the same 10 rows.
>
> Thanks for this suggestion, but what I'm really trying to do is return a
> cursor to a client program on a query like (this is pseudo-SQL -- I'm hoping
> to emulate TOP n using the rownum trick):
>
> select x,y,z from
> (select top 10 a as x, b as y, c as z from t1 order by ...
> union
> select top 10 p as x, q as y, r as z from t2 order by ...
> union
> select top 10 e as x, f as y, g as z from t3 order by ...
> )
>
> Another case of interest arises from the need to scroll pagewise through a
> large list, directed by the remote user over the Web (so there is no state
> on any connection), where the 10th page might be accessed by the query:
>
> select x,y,z from
> (select x,y,z from t1 order by ...)
> where rownum > 900 and rownum <= 1000
>
> I wouldn't want to open the cursor on the inner query in the client, read
> 1000 rows and throw away the first 900. (It's bad enough to have to execute
> the inner query for each page in the server).
>
> Jeff
Received on Sun May 14 2000 - 00:00:00 CDT