Re: Select from a function that returns a CURSOR

On Oct 3, 11:26 am, ronscottlang..._at_yahoo.com wrote:
> I have an outer query with subquery something like
>
> select * from
> (select * from dataset ds where ds.periodType = pt)
>
> The inner subquery in my real code is actually much more complex and
> needs to be used as an inner query in several other places. I would
> like to move this inner query to a re-useable function (or
> something...)
>
> I tried creating a function that returned a cursor and then do
> something like..
>
> select * from doQuery(pt)
>
> but I guess this is not possible. Can't really use a View since I
> need to pass parameters to the inner query.
>
> fyi, the returned data from the query can be very large, so probably
> best not to load into internal array or table.
>
> I guess if I was able to do something like this, then chance I may
> lose some of Oracle's optimizations without it knowing really what is
> being returned.
>
> Any suggestions? copy/paste :)
>
> Thanks,
> Ron

Pipelined functions, explained at http://tahiti.oracle.com and also at http://www.psoug.org/reference/pipelined.html.

David Fitzjarrell Received on Wed Oct 03 2007 - 11:36:17 CDT