Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Re: File upload to Oracle database using perl

by tfrayner (Curate)
on May 04, 2007 at 09:36 UTC ( #613532=note: print w/replies, xml ) Need Help??

in reply to File upload to Oracle database using perl


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


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://613532]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2020-10-28 00:39 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (259 votes). Check out past polls.