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

From: <adamvt_at_my-deja.com>
Date: Sun, 19 Nov 2000 20:48:11 GMT
Message-ID: <8v9eaa$t0k$1@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 Sun Nov 19 2000 - 14:48:11 CST