Re: oracle multiple cursor sub query in different DBI versions

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 <[email protected]> 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!