Hello, I found the following information on metalink.oracle.com that mentions that there was no solution as of March 2006.
Thanks. Biswa Subject: *OCCI Prefetch Does Not Work With REF CURSOR* Doc ID <https://metalink.oracle.com/help/usaeng/Search/search.html#file>: *Note:293030.1* Type: *PROBLEM* Last Revision Date: *28-MAR-2006* Status: *PUBLISHED* *In this Document* Symptoms<https://metalink.oracle.com/metalink/plsql/f?p=130:14:9270921068823151778::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,293030.1,1,1,1,helvetica#SYMPTOM> Cause<https://metalink.oracle.com/metalink/plsql/f?p=130:14:9270921068823151778::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,293030.1,1,1,1,helvetica#CAUSE> Solution<https://metalink.oracle.com/metalink/plsql/f?p=130:14:9270921068823151778::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,293030.1,1,1,1,helvetica#FIX> References<https://metalink.oracle.com/metalink/plsql/f?p=130:14:9270921068823151778::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,293030.1,1,1,1,helvetica#REF> ------------------------------ Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0 to 10.2.0 PL/SQL - Version: 9.2.0 to 10.2.0 This problem can occur on any platform. Symptoms The OCCI prefetch doesn't work invoking stored procedure with REF CURSOR. The OCCI programs that invoke stored procedures with REF CURSOR are very slow. Cause This is currently expected behavior, identified in Bug 3067376<https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3067376>PERFORMANCE ISSUE WITH OCCI CODE in which is reported that OCCI PREFETCH doesn't work with REF cursor However there have been raised an enhancement request to be implemented in the future releases for this in Bug:3140718 OCCI PREFETCH DOES NOT WORK WITH REF CURSOR Solution There is no solution available at the moment when writing this article (March 2006) to use PREFETCH with REF CURSORS. References Bug 3067376<https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3067376>- Performance Issue With Occi Code Bug 3140718<https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3140718>- Occi Prefetch Does Not Work With Ref Cursor On 9/22/07, Biswadeep Chowdhury <[EMAIL PROTECTED]> wrote: > > Hello, > > I had contacted Oracle through our DBA more than a year ago. > At that time, they had pointed me to bug that was fixed (attached). > However, that bug dealt with explicit-array-fetch and not the "prefetch" > setting that is being used in DBD. > > Also if we take some of the Oracle documentation very literally: > > The Pro*C documentation > athttp://download.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_06sql.htm#i4711 > > mentions "...The number of rows set by the PREFETCH option value is used > for all queries involving explicit cursors,..." > > The OCI documentation at > http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10779/oci04sql.htm#428254 > mentions "...the OCI can prefetch result set rows when executing a query > ..." > > Then it looks like the prefetch setting may not be intended to work for > REF CURSORs or nested cursors - although that is hardly reasonable. > > I will see if I can contact Oracle once more in the near future. > > Biswa > > > On 9/22/07, Tim Bunce <[EMAIL PROTECTED]> wrote: > > > > On Sat, Sep 22, 2007 at 01:22:50AM +0530, Biswadeep Chowdhury wrote: > > > > > > I have been looking for a fix for more than a year and have found > > none. > > > > Have you asked Oracle? > > > > Tim. > > > > >
