help! ora_lob_append / ora_lob_write fails when writing to a blob column in Oracle

Hi,

I'm trying to insert a file to a blob column. I can get upto retrieving
the blob locator but when i'm writing the contents of the file via
ora_lob_write or ora_lob_append, the procedure fails and I get the
error - "Error on ora_lob_append writing to filestorage table in
database" which I specified.

I've written the following code which I picked up from online
documents. I'm using Oracle8i EE.
Please help.

use DBD::Oracle qw( :ora_types);

my $dbh = DBI->connect( 'dbi:Oracle:host', 'test, 'testpass', {
RaiseError => 1, AutoCommit => 0 } ) || die DBI->errstr;

my $sth = $dbh->prepare("SELECT FileID.NEXTVAL FROM Dual");
$sth->execute;
my $fileid = $sth->fetchrow_array;

$sth = $dbh->prepare("INSERT INTO filestoragetest (FILEID, DATA)
VALUES (?, ?)");
$sth->bind_param(1,$fileid);
$sth->bind_param(2,'',{ ora_type => ORA_BLOB});
$sth->execute();

$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT data FROM filestoragetest WHERE fileid = ? FOR UPDATE
SQL
$sth->execute( $fileid );
my $bin_locator = $sth->fetchrow_array();
$sth->finish;

open BINFILE, "testupload.txt" or die $!;
my $buffer ='';
my $chunk_size = 4096;

eval {
while( read( BINFILE, $buffer, $chunk_size ) ) {
$dbh->ora_lob_append( $bin_locator, $buffer );
}
};

if ($@) {
$dbh->rollback;
$error .= "Error on ora_lob_append writing to filestorage
table in database. \n";
$error .= $dbh->errstr;
} else {
$dbh->commit;
}
close(BINFILE);
$sth->finish;

.

Relevant Pages

  • Generic code for inserting data to a BLOB column
    ... I want to insert value in the BLOB column of a table.right now i am ... using Weblogic as my appserver and oracle as database, ...
    (comp.lang.java.databases)
  • Re: Store an image
    ... "clara" schrieb: ... image operation with database both in Access and SQL Server2005. ... Read and Write a File to and from a BLOB Column by Using ADO.NET and ...
    (microsoft.public.dotnet.languages.vb)