SELECT from Function that return CURSOR

Question

I would like to select from a function that return a SYS_REFCURSOR Type value.

for example :

CREATE OR REPLACE FUNCTION my_funtion (
 my_param IN VARCHAR2)
 RETURN SYS_REFCURSOR
 IS
 l_return SYS_REFCURSOR;
 BEGIN
 OPEN l_return FOR
 SELECT last_name, first_name
 FROM employees
 WHERE id = my_param
 ORDER BY employee_id;
 RETURN l_return;
END my_funtion;

I would like to do something similar to this :

select * from my_function('id015');

or even :

select alias.last_name from my_function('id015') alias;

• Mat: Interesting read here: community.oracle.com/thread/888365?tstart=0

Answer 1

You could try using select * from table(xmlsequence( myfunc() )) . In this case you get xml in columns.

Or try this example to extract fields from your refcursor

select extractvalue(column_value,'/ROW/FIRST_NAME') first_name
 , extractvalue(column_value,'/ROW/LAST_NAME') last_name
from table(xmlsequence(f()));

FIRST_NAME | LAST_NAME
:--------- | :--------
Donald | Duck 
Mickey | Mouse 

dbfiddle here

PS. Note about xml overhead.

• Amir Pashazadeh: Does this solution closes the sys_refcursor internally?

Answer 2

This worked for me:

variable x refcursor 
set autoprint on 
exec :x := my_function('id015')