The fact that you're dealing with an Oracle database is really neither here nor there, except that you'll probably be storing the data in a column on type BLOB. This can be handled pretty much transparently with DBI.

When you talk of "uploading", I assume you mean a web form. If the site in question is inside a corporate LAN, the most efficient use of programmer time (what efficiency were we talking about again?) will be to use CGI to handle the decoding of the POST or GET data correctly.

You also want to prepare the SQL insert statement and use placeholders: if the query is hit with any regularity the optimiser will pull the previous query plan out of the library cache. This probably won't change the program efficiency one whit (because network latency is going to be your biggest problem by an order of magnitude), but it's a good habit to get into, and will serve you well in the future.

When all is said and done, you can probably do this in about 10 lines of simple code in Perl. If you have any particular questions on how to put it all together, don't hesitate to ask, if you get stuck on a some aspect of the problem.

Hell, given a page with a file upload input widget named 'upload', it would probably look something like (warning: untested code ahead):

use strict;
use warnings;

use CGI;
use DBI;

my $q = CGI->new;
my $db = DBI->connect( ... ); # see DBD::Oracle for details
END { $db and $db->disconnect };

my $insert = $db->prepare( <<END_SQL ) or die $db->errstr;
insert into tabname (colname) values (?)
END_SQL

if (defined(my $data = $q->param('upload'))) {
    $insert->execute($data)
        or die $insert->errstr;
}
[download]

By default, DBI will do commits automatically. You would be wise to turn this off, and do an explicit commit if everything goes according to plan, otherwise rollback. That is left as an exercise to the reader.

The most efficient way is to not do it in the first place. Relational databases make mediocre filesystems (just like filesystems make mediocre databases :). Store the path to the real file instead.

Thank You for the responses. Regards, Jasmine

Upload? Is the file being parsed into data to be loaded into tables, or are you trying to store the contents of the file as a blob of data?

In either case, DBI and DBD::Oracle are a good start. After that it depends on exactly what you are trying to end up with.

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]

Tim