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

adamvt_at_my-deja.com wrote:
>
> 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:
 ....
>
> 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.

If you get Oracle Magazine, have a look in the Ask Tom section for November/December.

Something along the lines of

SQL> create or replace type myScalarType 2 AS OBJECT (ename VARCHAR2(10), manager VARCHAR2(10), hiredate DATE);
3 /

Type created

SQL> CREATE OR REPLACE type myTableType
2 AS TABLE OF myScalarType;
3 /

Type created

SQL> CREATE OR REPLACE FUNCTION getMgrData (p_deptno IN NUMBER) 2 RETURN myTableType
3 AS
4 t_data myTableType := myTableType(); 5
5 CURSOR c1 IS
6 SELECT e.ename, m.ename AS MANAGER, e.hiredate 7 FROM emp e,
8 emp m
9 WHERE e.mgr = m.empno
10 AND e.deptno = p_deptno;
11
11 i NUMBER := 1;
12
12 BEGIN

13      FOR r1 IN c1 LOOP
14         t_data.extend;
15          t_data(i) := myScalarType(r1.ename, r1.MANAGER,
r1.hiredate);
16         i := i+1;
17      END LOOP;

Function created

The Select is *ahem* odd.

SELECT *
FROM THE(SELECT CAST (getMgrData(10) as myTableType) FROM dual);

Also, and I'm not sure whether this is superfluous or not, I tend to add USERENV('sessionid') as seqno into the types and select statement.

For a similar type of function for an array have a look at

http://www.jlcomp.demon.co.uk/dummy.html

HTH, MF

-- 
matt_foster_uk_at_yahoo.co.uk