RE: DBD:Oracle 1.07 & ref cursor

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:Tim.Bunce@xxxxxxxxx]
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


.