Re: Potential problem with DBD::Oracle

[Martin J. Evans]

On 18/11/2010 20:15, Steve Baldwin wrote:
Hi,
I ran across an issue recently that appears to have existed for quite some
time.

Consider the following script ...

#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;

sub main {
     my $dbh = DBI->connect(
         'dbi:Oracle:',
         'usr/p...@conn',
         '',
         { PrintError =>  0, AutoCommit =>  0, RaiseError =>  1 },
         );
     my $sql =<<'END_SQL';
SELECT  1 srt, 'AA' txt
FROM    dual
UNION
SELECT  2, 'BBB'
FROM    dual
UNION
SELECT  3, 'CCCC'
FROM    dual
ORDER
BY      1
END_SQL
     my $sth = $dbh->prepare($sql);
     $sth->execute;
     my ($srt, $txt);
     $sth->bind_columns(\($srt, $txt));
     while ($sth->fetch) {
         print "[$srt][$txt] len=" . (length($txt)) . "\n";
     }
     $dbh->disconnect;
     return 0;
}

exit main();

Running it produces the following output :

au-stb-101-144:dev stbaldwin$ ./sb2.plx
[1][AA] len=2
[2][BBB] len=2
[3][CCCC] len=2

As you can see, even though the data in $txt looks correct, perl thinks the
internal length of the variable is whatever it was after fetching the first
row.  This screws up things like sprintf.

I'm pretty sure this behaviour is a DBD::Oracle thing rather than a DBI
thing.  I tried an equivalent script with DBD::SQLite and DBD::mysql and
they both returned the length correctly (imo).

This behaviour exists in 1.23 and 1.26.  I haven't tested any other
versions.

Thanks,

Steve

I vaguely remember hitting this issue myself some time ago. Perhaps a 
search of dbi-users or dbi-dev will find something. I'll try and dig out 
a reference tomorrow.

Martin