I've found that PIPELINED functions work really well. From Perl you simply
say something like :
my $sth = $dbh->prepare(q(
SELECT *
FROM TABLE(mypipeline_function)
));
Steve
> -----Original Message-----
> From: Tim Bunce [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, 10 October 2006 6:54 PM
> To: Eugene Krivdyuk
> Subject: Re: DBD:Oracle 1.07 & ref cursor
>
> On Mon, Oct 09, 2006 at 04:49:12PM +0300, Eugene Krivdyuk wrote:
> > Hi all.
> >
> > I'm having trouble with stored pl/sql function which returns ref cursor.
> > This stored proc runs for 6 minutes while SQL itself executed for 6
> > seconds when executed outside pl/sql function.
>
> Possibly due to a bug where row caching isn't enabled for ref cursors.
>
> > Is there any way to speed things up without rewriting app without
> > packages/stored procedures ?
>
> Avoid fetching data directly from an sth created from a ref cursor.
>
> You could, for example, use some pl/sql to copy the data from the ref
> cursor into a temp table, then use DBI to fetch from that. There may
> also be a more direct way (avoiding the temp table) but I'm not that
> familiar with pl/sql. Perhaps others here can help.
>
> Tim.
>
> > P.S. I know that DBD::Oracle 1.07 is VERY old version but unfortunately
> > I can't update it to newest one on production server...
> >
> > Oracle server version is 9.2.0.6.0
> >
> > Thanks in advance.
> >
> > --
> > With Best Regards,
> > Eugene Krivdyuk
