[DBI-users] oracle multiple cursor sub query in different DBI versions

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-
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-
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