Downloading files from an Oracle Directory on Amazon RDS

On Amazon RDS, you can upload dumpfiles with a Perl script as described in This Amazon RDS document (see pages 24/25). This works after a little rewrite for some linebreaks and syntax inconsistencies. The script enables you to upload and subsequently import your dumpfile using dbms_datapump on your Amazon RDS Oracle database.

But what if you want to create a datapump export and download the resulting dumpfile(s)? So far I have not found a working example that fits my needs, so I decided to create a new script. As I have no real Perl experience, it will most probably not be an award winning script, but it does the job.

# INPUT parameters:
# 1 – the full name of the RDS server
# 2 - the filename to be downloaded from RDS
# 3 – the password for the DBA user
use strict;
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
# turn off screen buffering (for displaying progress)
$|++;
# RDS instance info
my $RDS_PORT=1521;
my $RDS_HOST=$ARGV[0];
my $RDS_LOGIN_USER="dba_user";
my $RDS_LOGIN_PW=$ARGV[2];
my $RDS_SID="ORCL";
my $dirname = "DATA_PUMP_DIR";
# Timing variable - start time
my $sttime = time;
my $loopcount = 0;
my $totalcount = 0;
my $totalsize = 0;
# Rest of the variables
my $fname = $ARGV[1];
my $data;
my $chunk = 32767; #max READ size
my $sql_open = "BEGIN perl_global.fh := utl_file.fopen(:dirname, :fname, 'rb', :chunk); END;";
my $sql_read = "BEGIN utl_file.get_raw(perl_global.fh, :data, NULL); EXCEPTION WHEN NO_DATA_FOUND THEN :datalength := 0; WHEN OTHERS THEN RAISE; END;";
my $sql_close = "BEGIN utl_file.fclose(perl_global.fh); END;";
my $sql_global = "create or replace package perl_global as fh utl_file.file_type; end;";
my $conn = DBI->connect('dbi:Oracle:host='.$RDS_HOST.';sid='.$RDS_SID.';port='.$RDS_PORT, $RDS_LOGIN_USER, $RDS_LOGIN_PW) || die ( $DBI::errstr . "\n") ;
my $updated=$conn->do($sql_global);
my $stmt = $conn->prepare ($sql_open);
# Processing the file
$stmt->bind_param_inout(":dirname", \$dirname, 12);
$stmt->bind_param_inout(":fname", \$fname, 12);
$stmt->bind_param_inout(":chunk", \$chunk, 4);
$stmt->execute() || die ( $DBI::errstr . "\n");
open (INF, '>', $fname) || die "\nCan't open $fname for writing: $!\n";
binmode(INF);
$stmt = $conn->prepare ($sql_read);
my %attrib = ('ora_type','24');
my $datalength=1;
print "\nTransferring file: \n";
while ($datalength > 0) {
  $stmt->bind_param_inout(":data", \$data , $chunk, \%attrib);
  $stmt->bind_param_inout(":datalength", \$datalength , 4);
  $stmt->execute() || die ( $DBI::errstr . "\n");
  if ($datalength > 0) {
    print INF $data;
    $loopcount++;
    $totalcount++;
    # refresh display after 320 chunks of 32k bytes = 10 Mb
    if ($loopcount == 320) {
      $totalsize = $totalcount*32/1024;
      print "\r# $totalsize Mb written";
      $loopcount = 0;
    };
  };
};
# Wrap up
$totalsize = $totalcount*32/1024;
print "\r# $totalsize Mb written (done)\n";
close INF || die "Can't close $fname: $!\n";
$stmt = $conn->prepare ($sql_close);
$stmt->execute() || die ( $DBI::errstr . "\n");
#
my $entime = time;
my $elapse = $entime - $sttime;
print "\nElapsed time : $elapse seconds\n\n";


The database/instance name, the DBA database user and port are hardcoded in the script, and I added some parameters. This was sufficient for my situation, and can of course be modified as needed. Just modify the section "RDS Instance Info" or add additional parameters.

I added some "features", like a progress tracker that updates every 10 Mb downloaded content. Not really necessary, but for larger files I like to know what he is doing and far along the download is. All in all this enables me to directly download the dumpfiles (and other files of course) without having to use the dbms_file_transfer (to get it to another database) or the limited "SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DIR','file.txt'))" for reading text files.

Now, just call it from the commandline, for example:
perl /home/oracle/scripts/download_from_RDS.pl rds_server_name filename