I have a function returning an open SYS_REFCURSOR. The function builds and executes a few different SQL queries and returns that cursor.
I am using that cursor in PHP to fetch the results, but the performance is not to good, as the cursor data cannot be prefetched and for each row there is round trip to database server.
I think I would speed things up with by creating pipelined function which would get the cursor as in parameter and pipeline the rows.
How to write a table function that gets any sys_refcursor as parameter and outputs collection of any rowtype of that cursor?
So long I have written pipelined table functions but they all have been strongly typed. In this situation I would rather not write n-times the row type, table type and separate table function for each of possible cursor types...
EDIT
The issue with fetching data from SYS_REFCURSOR was buried in our php code.
Prefetching is working with OCI since version 11gR2.
As I have fixed the prefetching bug, the performance is sufficient and I have no more need to develop generic table function.
Thank you all for your support and comments.