Quick and Dirty Hacks, 3

• Suppose your database doesn't have a decent bulk uploader, or at least, not one you can understand easily (Oracle's SQL*Loader springs to mind), and
• Someone has given you a tab separated file to upload. Easy!

#!perl -n
## Example use: 
## ./bulk-upload.pl user/pass@sid table cola colb < bulk-file
use strict;
use vars qw!$table @columns $dbh $sth!;
BEGIN {
  use DBI;
  $dbh = DBI->connect("dbi:Oracle:", shift, '', {
    'AutoCommit' => 'false'
  });
  $table = shift;
  @columns = @ARGV;
  $sth = $dbh->prepare(
    "insert into $table (", join(",", @columns), ") values (",
    join(",", ("?") x @columns), ")"
  );
}

  my @values = split /\t/, $_;
  unless ($sth->execute(@values)) {
    print "Failed on line $., values @values: " . $dbh->errstr;
  };

END { $dbh->commit }

Important caveats:

• If the above breaks, you get to keep both parts.
• It will likely be very slow.
• If you know how to use, for example, Oracle's SQL*Loader, use that instead -- it's really much faster.