0

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.

SWilk
  • 3,261
  • 8
  • 30
  • 51
  • 1
    Dynamic cursors should be seen/used as any other cursor by external programs (static cursors only exist inside Oracle). Also prefetching seems to be [enabled by default in OCI 8](http://docs.oracle.com/cd/E17781_01/appdev.112/e18555/ch_eight_query.htm). Furthermore, what happens if you specify prefetch *before* executing the cursor in PHP? Finally, you can't return a generic array type in Oracle, you'll have to specify its column structure. – Vincent Malgrat Sep 06 '13 at 09:51
  • 3
    why do you need a function to return rows (of varying rowtypes) to you? would like to see the function code, but I suspect you created some really generic function like: get_data('EMP') or get_data('DEPT'), and return a generic refcursor. Why not simply select from emp or dept as needed? But maybe I misunderstand the situation (posting some code would help) – tbone Sep 06 '13 at 10:51
  • @tbone if your interface to the data is in stored procedures, it's frequent to expose data through cursors opened by the database. This way you don't have to grant rights on the base table to the web app. – Vincent Malgrat Sep 06 '13 at 12:48
  • @VincentMalgrat I understand, but many times people just over-engineer the solution (hey, wouldn't it be cool if I had this uber function that worked for any type of data, I could call it for anything! Oh wait, why does performance suck?). No offense SWilk, not saying this is your situation, but it wouldn't be the first time I've seen it either. Again, some code would help – tbone Sep 06 '13 at 12:57
  • @tbone I agree, it looks a bit over-engineered. Furthermore, I don't see how a select over a pipelined function that opens a ref cursor could perform better than a straight (ref) cursor! – Vincent Malgrat Sep 06 '13 at 13:07
  • @VincentMalgrat, @tbone, The situation is complicated. I have some very complicated views. The views returns hundreds of thousands of rows, one of them returns milions when not filtered. And the filtering column cannot be simply used in `where` clause, as it is selected in subselects. The only possible solution to speed those views was to filter them at deepest possible level of sql, and that works. But views cannot be parametrized. Hence I converted the views to cursors. The views returns a few thousands of rows when filtered, so they still need to be as fast as possible. – SWilk Sep 06 '13 at 13:31
  • Anyway, the issue was in our oci abstraction in PHP. It incorrectly set prefetching for cursors. And it was not tested enaugh. Googling around I have read that prefetching did not work for cursors in PHP. Then I have found that it was fixed in OCI 11gR2, and we are using this version for some time now, so I started to look in our php code and found the bug. – SWilk Sep 06 '13 at 13:34
  • @swilk Are you absolutely sure that the performance problem you're experiencing comes from the prefetch issue? Cursors are cursors and should be treated as regular result set by external tools, whether they are static or dynamic. Edit: saw your last comment, does it mean you can fix the prefetch issue? – Vincent Malgrat Sep 06 '13 at 13:38
  • Yes, I have fixed it. It was in our internal library abstracting `oci_*` php functions into classes. It seems that some guy, who worked here years ago and wrote the library, have developed the executeCursor method actually set the prefetch to **1** row. I do not know why he had done that. Anyway, at the time the library was build we were using oracle 9i which did not support prefetching of ref_cursors. – SWilk Sep 06 '13 at 14:49

0 Answers0