Re: How to use ref curs function in a SELECT or VIEW?

It is not possible,
as it is not possible *by design* to return more than one result from a function, use a procedure instead.
IMO, this could easily be done with one sql-statement, you seem to be adding extra levels of complexity.
Also, a function with a select called in a select, will not be optimized away. You will run into performance problems.

Regards,

Sybrand Bakker, Oracle DBA

<adamvt_at_my-deja.com> wrote in message news:8veh8d$5i$1_at_nnrp1.deja.com...
> Is it posible to use a function which returns "ref curs" data type in a
> SELECT or VIEW? My objective is to get more than one value from the
> RETRUN of a function. Here is an exmaple:
>
> CREATE OR REPLACE PACKAGE SCOTT.refCur3 as
> cursor c2 is select ename, ename AS manager, hiredate from emp;
>
> type mgrCur is ref cursor return c2%ROWTYPE;
>
> function GetMgrData(indeptno IN NUMBER)
> RETURN mgrCur;
> END;
> /
>
> CREATE OR REPLACE PACKAGE BODY SCOTT.refCur3 as
>
> function GetMgrData(indeptno IN NUMBER )
> RETURN mgrCur is
> MgrCursor mgrCur;
> begin
> open MgrCursor for select e.ename, m.ename AS MANAGER, e.hiredate
> from emp m, emp e
> where e.mgr= m.empno and e.deptno= indeptno;
> return MgrCursor;
> end;
> end;
> /
>
> Now can I do something like this which of course did not work for me:
>
> select refCur3.GetMgrData(7369) from dual;
>
> I mean a similar concept which will work? I know I can get a
> concatenated result from the function then parse it, but I would like
> to know if it is possible with a "Ref curs" function.
>
> Thanks,
> Adam.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Nov 21 2000 - 20:54:38 CET