Hi,
I concur with Fletch that the filesystem is a better place to put files, with a pointer to the filesystem path stored in the actual database. However, we use the following to store files in BLOB fields in our Oracle DB:
use strict;
use warnings;
use DBI;
require DBD::Oracle;
# Filename and field ID on the command line.
my ($filename, $dbid) = @ARGV;
my $dbh = DBI->connect("DBI:Oracle:host=your.hostname.here:sid=YOURDBN
+AME:port=8080",
'your_username',
'your_password',
{LongReadLen => 100_000_000, RaiseError => 1});
my $sth = $dbh->prepare("select your_blob_field from your_table where
+your_id_field=? for update", {ora_auto_lob => 0});
$sth->execute($dbid) or die($sth->errstr);
my $char_locator = $sth->fetchrow_array();
$sth->finish();
die("No record found") unless $char_locator;
open(my $fh, '<', $filename) or die("Error opening file: $!");
my $chunk_size = 4096; # Arbitrary chunk size
# Write file to BLOB record.
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = q{};
while( $length = read( $fh, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write( $char_locator, $offset, $buffer );
$offset += $length;
}
close($fh);
# If the new BLOB in DB was smaller than the original, adjust the
# size of the BLOB field
if ( $offset < $dbh->ora_lob_length( $char_locator ) ) {
$dbh->ora_lob_trim( $char_locator, $offset - 1 );
}
$dbh->disconnect();
Cheers,
Tim