I'm running DBD1.06 on a Linux machine. All worked well as long as we used
Oracle client 8.1.5. Once we made the change to 8.17, we failed to read
CLOB's continuously. An example script with output for 8.15 and 8.1.7
follow:
#---------------------------------------------------------------------------
--------------------------------------------------------------
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $blob="C" x 10200;
print "Starting Length=", length $blob, "\n";
print "--Step 1 done --\n";
my $dbh = DBI->connect("dbi:Oracle(PrintError=>0,AutoCommit=>0):".
"DB_NAME","USER","PASSWORD")
|| die "couldn't connect to db";
# drop table but don't die if not there
$dbh->do("drop table foobar");
print "database now open\n";
$dbh->do("CREATE TABLE foobar (foo NUMBER, bar CLOB)");
my $sth = $dbh->prepare("insert into foobar values (?, ?)");
$sth->bind_param(1, "3");
$sth->bind_param(2, $blob, {"ora_type", ORA_CLOB});
$sth->execute()|| die $DBI::errstr;
$sth->finish;
$dbh->{RaiseError} = 1;
$dbh->{LongTruncOk} = 1; # truncation on initial fetch is ok
$sth = $dbh->prepare("SELECT foo, bar FROM foobar WHERE foo = ?")
|| die $dbh->errstr;
$sth->execute(3)|| die $dbh->errstr;
my $key;
($key) = $sth->fetchrow_array();
my $offset = 0;
my $lump = 4096;
my @frags;
while (1) {
my $frag = $sth->blob_read(1, $offset, $lump);
last unless defined $frag;
my $len = length $frag;
last unless $len;
push @frags, $frag;
$offset += $len;
}
$blob = join "", @frags;
print "$key: $blob\n";
$sth->finish;
$dbh->commit();
$dbh->disconnect;
#---------------------------------------------------------------------------
--------------------------------------------------------------
results on Oracle 8.1.7 are:
Starting Length=10200
--Step 1 done --
database now open
DBD::Oracle::st fetchrow_array failed: (DBD NEED_DATA: OCILobRead) at
./testClob2.pl line 41.
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
results on Oracle 8.1.5:
Starting Length=10200
--Step 1 done --
database now open
3: CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC (... around 10200 'C's)
Similar results follow when using regular fetchrow_hashref and such, without
blob_read.
Any advice?
While I'm at it, is blob_read the "new" standard, or is it slowly becoming
deprecated? Is it well documented somewhere?
Thanks,
Gilad.
