A little slow on this one and I apologize for that.
Just closing things out for 1.28 version of DBD::Oracle and noticed this one
hanging about.
So I though I would give it a few tests
I checked 1.24 and it fails but it seems to be fixed in 1.62 and in trunk
So I will check this one off my list for 1.28
Cheers
John Scoles
On Wed, Aug 4, 2010 at 2:27 PM, LK <lok...@gmail.com> wrote:
> In the process of moving from centos 4 to a centos 5 machine one
> script stopped working. I distilled it down to this problem :
>
>
> #! /usr/bin/perl -w
> use strict;
> use DBI;
>
> my $dbh = DBI->connect("dbi:Oracle:host=SSSS;sid=SSSS", 'XXXX',
> 'XXXX',
> {RaiseError => 1,
> AutoCommit => 0,
> LongReadLen => 5000
> }
> ) || die "Database connection not made:
> $DBI::errstr";
>
> my $q = qq/
> SELECT
> (2+3),
> cursor(select 2+3 from dual),
> cursor(select 3+3 from dual)
> FROM table where rownum < 10
> /;
>
> my $sth = $dbh->prepare($q);
> $sth->execute();
> while ( my @d = $sth->fetchrow_array ) {
> print join(" " , @d) . "\t";
> while ( my @g = $d[1]->fetchrow_array ) {
> print join(" " ,@g) . "\t";
> }
> while ( my @g = $d[2]->fetchrow_array ) {
> print join(" " ,@g ). "\t";
> }
> print "\n";
> }
>
> no problem in sqlplus for this query in either machine.
>
> On the server with DBI 1.58 , DBD::Oracle 1.19 and oracle instant
> client 10.1 it works fine.
>
> On the server with DBI 1.611 , DBD::Oracle 1.24b and oracle instant
> client 11.1 it returns nothing, with no errors.
>
> Use of DBI_TRACE returns nothing I can work with, it just stops on
> the newer server:
>
> -> prepare for DBD::Oracle::db
> (DBI::db=HASH(0x1be48c80)~0x1be48c40 '
> SELECT
> (2+3),
> cursor(select 2+3 from dual),
> cursor(select 3+3 from dual)
> FROM table where rownum < 10
> ') thr#1bb1a010
> dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
> dbd_describe SELECT (EXPLICIT, lb 5000)...
> Described col 1: dbtype 2(NVARCHAR2), scale 0, prec 0, nullok 1, name
> (2+3)
> : dbsize 2, char_used 0, char_size 0, csid 0, csform
> 0(0), disize 171
> fbh 1: '(2+3)' NULLable, otype 2-> 5, dbsize 2/172, p0.s0
> Described col 2: dbtype 116(SQLT_RSET OCI 8 cursor variable), scale
> 0, prec 0, nullok 1, name CURSOR(SELECT2+3FROMDUAL)
> : dbsize 8, char_used 0, char_size 0, csid 0, csform
> 0(0), disize 8
> fbh 2: 'CURSOR(SELECT2+3FROMDUAL)' NULLable, otype 116-
> >116, dbsize 8/8, p0.s0
> Described col 3: dbtype 116(SQLT_RSET OCI 8 cursor variable), scale
> 0, prec 0, nullok 1, name CURSOR(SELECT3+3FROMDUAL)
> : dbsize 8, char_used 0, char_size 0, csid 0, csform
> 0(0), disize 8
> fbh 3: 'CURSOR(SELECT3+3FROMDUAL)' NULLable, otype 116-
> >116, dbsize 8/8, p0.s0
> cache settings DB Handle RowCacheSize=0,Statement Handle
> RowCacheSize=0, OCI_ATTR_PREFETCH_ROWS=339,
> OCI_ATTR_PREFETCH_MEMORY=0, Rows per Fetch=339, Multiple Row Fetch=On
> dbd_describe'd 3 columns (row bytes: 18 max, 20 est avg,
> cache: 0)
> <- prepare= DBI::st=HASH(0x1be48ee0) at trek_cursor_query.pl line
> 33
> -> execute for DBD::Oracle::st
> (DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
> dbd_st_execute SELECT (out0, lob0)...
> Statement Execute Mode is 0 (DEFAULT)
> rs_array_init:imp_sth->rs_array_size=2, rs_array_idx=0,
> prefetch_rows=0, rs_array_status=SUCCESS
> dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
> <- execute= '0E0' at trek_cursor_query.pl line 34
> -> fetchrow_array for DBD::Oracle::st
> (DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
> dbd_st_fetch 3 fields...
>
>
>
> Removing one of the cursor statements makes it work on both machines!
> I am out of ideas.
>
> Any ideas are most appreciated!
>
> Thanks,
> LK
>
>
>
>
>
>
>
>
--
>From the Pythian family to yours, Happy Holidays and all the best in 2011!
