Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
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 examining the Monastery: (12)
As of 2015-07-07 20:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (93 votes), past polls