#!/usr/bin/perl -w
use strict;
use DBI;
use Getopt::Long;
my ( $user, $passwd, $db ) = ( "scott", "tiger", "local" );
my ( $id, $blob, $file ) = (1);
my $stat = GetOptions( "u|user=s"     => \$user,
                       "p|password=s" => \$passwd,
                       "d|database=s" => \$db,
                       "i|id=s"       => \$id,
                       "h|help|?"     => \&usage
);
if ( !$stat ) { usage(); }
my $dbh = db_connect( $user, $passwd, $db );
my $SEL = "SELECT name,data FROM dbi_lob WHERE id=:ID";
my $sth = $dbh->prepare($SEL);
$sth->bind_param( ":ID", $id );
$sth->execute();
($file,$blob)=$sth->fetchrow_array();
spew($file);
print "File $file written successfully. Len=",length($blob),"\n";

END {
    $dbh->disconnect() if defined($dbh);
}

sub db_connect {
    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );
    my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )
        || die( $DBI::errstr . "\n" );
    $dbh->{AutoCommit}    = 0;
    $dbh->{RaiseError}    = 1;
    $dbh->{ora_check_sql} = 0;
    $dbh->{RowCacheSize}  = 16;
    $dbh->{LongReadLen}   = 5242880;
    $dbh->{LongTruncOk}   = 0;
    return ($dbh);
}

sub usage {
    print qq(
      USAGE:$0
               -u <username>  -p <password > -d <database>
               -i id
      -------
      This script unloads blob with id defined by -i parameter from the
      database. This script was written as an educational tool and is
      free to use and modify as needed.
);
    exit(0);
}

sub spew {
    my $file = shift;
    if ( !defined($file) ) { usage(); }
    open( FL, ">", $file ) or die "Cannot open file $file for writing:$!\n";
    printf FL ("%s",$blob);
    close FL;
}