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

From: <adamvt_at_my-deja.com>
Date: Tue, 21 Nov 2000 19:09:10 GMT
Message-ID: <8veh8d$5i$1_at_nnrp1.deja.com>


[Quoted] [Quoted] 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;

[Quoted] 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:09:10 CET

Original text of this message