| Subject: | null CLOB is returned as "LobLocator" and not as undef |
If I select NULL and empty CLOB from SELECT, DBD::Oracle retuns "undef"
and empty LobLocator (or string) - it is correct.
But if I use output parameter, it returns LobLocator also for "NULL":
my $lob1;
my $sth = $dbh->prepare('BEGIN :lob1 := NULL; END;', {ora_auto_lob =>
0});
$sth->bind_param_inout(":lob1", \$lob1, 0, { ora_type => ORA_CLOB });
$lob1 contains LobLocator (or string) and not undef;
Same bug is with {ora_auto_lob => 0} and also with {ora_auto_lob => 1}.
-------------------------
Testing script (attached)
-------------------------
#!/usr/bin/env perl
use DBD::Oracle ':ora_types';
use DBI;
use Data::Dumper;
print "DBD::Oracle version: $DBD::Oracle::VERSION\n\n";
my ($user, $passwd) = split '/', $ENV{ORACLE_USERID};
my $dbh = DBI->connect($ENV{ORACLE_DSN}, $user, $passwd,
{ AutoCommit => 0, RaiseError => 1, PrintError => 1
});
$dbh->do('CREATE TABLE t1 (a CLOB, b CLOB)');
$dbh->do('INSERT INTO t1 VALUES (NULL, EMPTY_CLOB())');
$dbh->commit;
# Selecting NULL CLOB in cursor work ok
my $sth = $dbh->prepare('SELECT a, b FROM t1', {ora_auto_lob => 0});
$sth->execute();
my ($a, $b) = $sth->fetchrow_array();
print "NULL CLOB in cursor: ".Dumper($a);
print "EMPTY CLOB in cursor: ".Dumper($b), "\n";
# but getting NULL CLOB in bind parameter does not work
print "With bind inout CLOB:\n";
my $lob1;
my $sth = $dbh->prepare('BEGIN SELECT a INTO :lob1 FROM t1; END;',
{ora_auto_lob => 0});
$sth->bind_param_inout(":lob1", \$lob1, 0, { ora_type => ORA_CLOB });
$sth->execute();
print "NULL CLOB: ".Dumper($lob1);
my $lob2;
my $sth = $dbh->prepare('BEGIN SELECT b INTO :lob2 FROM t1; END;',
{ora_auto_lob => 0});
$sth->bind_param_inout(":lob2", \$lob2, 0, { ora_type => ORA_CLOB });
$sth->execute();
print "EMPTY CLOB: ".Dumper($lob2), "\n";
$dbh->do('DROP TABLE t1');
-------------------------------------------------------
-------------------------------------------------------
--- Script OUTPUT: ---
-------------------------------------------------------
DBD::Oracle version: 1.26
NULL CLOB in cursor: $VAR1 = undef;
EMPTY CLOB in cursor: $VAR1 = bless( do{\(my $o = 164233148)},
'OCILobLocatorPtr' );
With bind inout CLOB:
NULL CLOB: $VAR1 = bless( do{\(my $o = 164232940)}, 'OCILobLocatorPtr'
);
EMPTY CLOB: $VAR1 = bless( do{\(my $o = 164232748)}, 'OCILobLocatorPtr'
);
| Subject: | test-lob.pl |
#!/usr/bin/env perl
use DBD::Oracle ':ora_types';
use DBI;
use Data::Dumper;
print "DBD::Oracle version: $DBD::Oracle::VERSION\n\n";
my ($user, $passwd) = split '/', $ENV{ORACLE_USERID};
my $dbh = DBI->connect($ENV{ORACLE_DSN}, $user, $passwd,
{ AutoCommit => 0, RaiseError => 1, PrintError => 1 });
$dbh->do('CREATE TABLE t1 (a CLOB, b CLOB)');
$dbh->do('INSERT INTO t1 VALUES (NULL, EMPTY_CLOB())');
$dbh->commit;
# Selecting NULL CLOB in cursor work ok
my $sth = $dbh->prepare('SELECT a, b FROM t1', {ora_auto_lob => 0});
$sth->execute();
my ($a, $b) = $sth->fetchrow_array();
print "NULL CLOB in cursor: ".Dumper($a);
print "EMPTY CLOB in cursor: ".Dumper($b), "\n";
# but getting NULL CLOB in bind parameter does not work
print "With bind inout CLOB:\n";
my $lob1;
my $sth = $dbh->prepare('BEGIN SELECT a INTO :lob1 FROM t1; END;', {ora_auto_lob => 0});
$sth->bind_param_inout(":lob1", \$lob1, 0, { ora_type => ORA_CLOB });
$sth->execute();
print "NULL CLOB: ".Dumper($lob1);
my $lob2;
my $sth = $dbh->prepare('BEGIN SELECT b INTO :lob2 FROM t1; END;', {ora_auto_lob => 0});
$sth->bind_param_inout(":lob2", \$lob2, 0, { ora_type => ORA_CLOB });
$sth->execute();
print "EMPTY CLOB: ".Dumper($lob2), "\n";
$dbh->do('DROP TABLE t1');