4

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;
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.

  • Does this solution closes the sys_refcursor internally? – Amir Pashazadeh Jan 15 at 15:26
0

This worked for me:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.