Re: selecting from functions returning resultsets

René Laursen wrote:

> I have some problems using functions that returns resultssets.
>
> I have a package looking something like this - I have removed the actual
> fields,tables and conditions to simplify it:
>
> CREATE OR REPLACE package StatView as
> type cursorType is ref cursor;
> function AktLonStatVirk (pAar IN smallint, pHalvaar IN
> smallint) return cursortype;
> PROCEDURE LonStatAfdBrVirk(pAar IN smallint, pHalvaar IN smallint, pResult
> OUT cursortype);
> end;
>
> CREATE OR REPLACE package body StatView as
> function AktLonStatVirk(pAar IN smallint, pHalvaar IN smallint) return
> cursortype
> IS
> pResult Cursortype;
> BEGIN
> open pResult for
> select * from <sometabel> where <somecondition based on parameters>;
>
> Return pResult;
> END;
>
> PROCEDURE LonStatAfdBrVirk(pAar IN smallint, pHalvaar IN smallint, pResult
> OUT cursortype) IS
> BEGIN
> open pResult for
> select <fieldlist> from table1, table2, /* Error: */
> table(AktLONSTATVIRK(pAar,pHalvAar)) Stat
>
> where <somecondition joining table1, table2 and Stat >
> END LonStatAfdBrVirk;
>
> I am getting PL/SQL: ORA-22905: cannot access rows from a non-nested table
> item at the position marked Error above.
>
> What am I doing wrong ?
> How do I select from a function returning a resultset. ?
> Can't I use such a funtion in a join ?
>
> In the example above I am able to write the contents of function
> "AktLonStatVirk" into the calling procedure but in fact I have to call this
> function twice with 2 different set of parameters from within the same
> select- statement.... (In order to compare statistics from different years)
>
> René /SSV

Since you choose to gut the SQL rather than present it I can't tell. Are you charged you by the byte to post?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)