use strict;
use warnings;

use DBI;
use DBD::Oracle qw(:ora_types);

print "Establishing DWASAS connection...\n";

# establish database connection and enable tracing option with dump to 'tracelog.txt'.
my $dsn = 'dbi:Oracle:DWASAS';
my @connection = ($dsn, $user, $pass, {InactiveDestroy => 1, PrintError => 0, RaiseError => 1});
my $dbh = DBI->connect(@connection) or die;
open(my $tracelog_fh, '>', 'tracelog.txt') or die "Cannot open \"tracelog.txt\": $!.";
$dbh->trace(4, $tracelog_fh);

# pepare and execute 'prepack_candidates.sql'
print "Preparing 'prepack_candidates.sql'...\n";
my $sth1 = $dbh->prepare($candidates_sql) or die;
print "Executing 'prepack_candidates.sql'...\n";
$sth1->execute() or die;

print "Fetching 'prepack_candidates.sql' returned data...\n";

print "\n----------\n";
my $print_format = '%7s   '.
                   '%-50s   '.
                   '%11s   '.
                   '%12s'
                   ;
printf("$print_format\n", 'ROW_NBR', 
                          'REPORT_PART_NBR', 
                          'SHIPPED_QYT', 
                          'COUNT_AT_QTY'
      );
my $row_counter = 1;
while(my $row = $sth1->fetchrow_hashref) {
    foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; # change all NULLs to empty strings
    printf("$print_format\n", $row_counter, 
                              $row->{'REPORT_PART_NBR'}, 
                              $row->{'SHIPPED_QTY'}, 
                              $row->{'COUNT_AT_QTY'}
          );
    $row_counter++;
}
print "----------\n";