Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

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

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


Comment on Re: File upload to Oracle database using perl
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2014-12-27 02:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (176 votes), past polls