Re: Potential problem with DBD::Oracle

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/pwd@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