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();

[download]
Cheers,

Tim

In reply to Re: File upload to Oracle database using perl by tfrayner
in thread File upload to Oracle database using perl by jasmine12345

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.