My function is being passed a SYS_REFCURSOR as a parameter. I need to use the values stored within it in one of my SELECT statements. If the REFCURSOR were a Collection I could do something along the lines of:
select *
from my_table msg
table(cast(my_ref_cursor as my_obj_type)) v
where msg.message_code = v.errorcode
How can I acheive something similar with a REFCURSOR?
I am currently using the LOOP/FETCH/EXIT/END LOOP construct to go through each record in the REFCURSOR and then using the ERRORCODE returned by each row in my SELECT statement however this seems a like a poor solution as the same SQL will be executed many times for each record in the REFCURSOR.
Any ideas as to how I can turn the REFCURSOR into a Collection or a set of values that I can then use in my SELECT statement?