SELECT from Function that return CURSOR

cursorsoracleplsqlselect

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;

Best Answer

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.

Related Question