Skip Menu |
 
rt.cpan.org will be shut down on March 1st, 2021.

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 62124
Status: rejected
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: tomas.zemres [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in:
  • 1.23
  • 1.25
  • 1.26
Fixed in: (no value)



Subject: null CLOB is returned as "LobLocator" and not as undef
Download (untitled) / with headers
text/plain 8.5k
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
Download test-lob.pl
text/x-perl 1.2k
#!/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');
Download (untitled) / with headers
text/plain 1.5k
spent a good three hours on this for you so here is why I rejected it In short the code is working as it should. When working with in_out params and lobs you have to give a hint to DBD::Oracle of what to expect in this case a clob which you do correctly with your code below my $sth = $dbh->prepare("BEGIN SELECT a INTO :lob1 FROM t1; END;", {ora_auto_lob => 0}); $dbh->{dbd_verbose}=0; $sth->bind_param_inout(":lob1", \$lob1, 0,{ ora_type => ORA_CLOB }); in the back end I have to init a OCILobLocator for it and that is being returned. However and here is the funny thing about Oracle lobs a OCILobLocator can have two states 1) initialized 2) uninitialized when you did your insert in you added 1 row with an uninitialized and initialized lobs, and yes, you guessed it, EMPTY_CLOB() will initialize a lob as null or empty. so if you change your Dumps to the following print "NULL CLOB: ".$dbh->ora_lob_is_init($lob1). "\n"; print "EMPTY CLOB: ".$dbh->ora_lob_is_init($lob2). "\n"; you will get NULL CLOB: 0 EMPTY CLOB: 1 which is correct. You have to remember that when you select a lob DBD::Oracle does a bunch of back end black-magic that takes the OCILobLocator (actually just a pointer to it but the same thing) and reads it returns your values. Using the BEGIN statement will drop you out of this auto mode and return in this case the OCILobLocator. Sorry no way around this as once you add a BEGIN you are in PLSQL which DBD::Oracle (or oci for that matter) has no control over. Hope this helps you out. Cheers John Scoles


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.