use DBI qw(:sql_types);
use strict;

require "DBUtil.pl";
require "BatchEnv.pl";

my $procName = shift;
my $cycleDate = shift;
my $frequency = shift;
my $finRepId = shift;
my $fromDate = shift;
my $toDate = shift;
my $showPendingPolicies = shift;
my $strConnectionString;
my $dsn;
my $myDSN;
my $user;
my $password;
my $dbh;
my $procedure;
my $function;
my $retCode;
my $retMsg;

if ($procName eq ''){
    #print "Usage: ExecuteProc.pl [Procedure Name] -o[CycleDate] -o[Fr
+equency] -o[Fromdate] -o[ToDate] -o[FinRepId] -o[ShowPendingPolicies]
+";
    #exit();
    
    # replaced with "die" - not necessary but saves a line, and may re
+turn valuable return code information
    die "Usage: ExecuteProc.pl [Procedure Name] -o[CycleDate] -o[Frequ
+ency] -o[Fromdate] -o[ToDate] -o[FinRepId] -o[ShowPendingPolicies]\n"
+;
}

print ("In the script\n");

# Open Database connection

$strConnectionString = &GetConnectionString;

# Don't explicitly set $_ here, it's unnecessary
#$_=$strConnectionString;
#if(/data source=([0-9A-Za-z]*;)user id=([0-9A-Za-z].+;)password=([0-9
+A-Za-z].+)/){

#changing the regexp to not capture the semicolons

if ( $strConnectionString =~ /data source=([0-9A-Za-z]*);user id=([0-9
+A-Za-z].+);password=([0-9A-Za-z].+)/ )
    $myDSN=$1;    $user=$2; $password=$3;
#    chop($myDSN); chop($user);
# We don't need the chops anymore because we're not capturing the semi
+colons
}
# else { what happens if they enter an invalid connection string? }
  
    $dsn ="dbi:Oracle:$myDSN";
    $dbh = DBI->connect ($dsn, $user, $password, {
        PrintError =>1,
# PrintError is usually turned off when RaiseError is set... it's redu
+ndant
        RaiseError =>1
        }) or die "Database connection not made: $DBI::errstr"; 

# This 'if' is unnecessary, since we already explicitly tested for thi
+s condition above
#if($procName ne ''){
        # This is the default call to stored proc.
    $procedure = "BEGIN "."$procName"."(?, ?); "."END;";
    print $procedure, "\n";
    print "After Connection\n";

    $function = $dbh->prepare($procedure);
    $function->bind_param_inout(1, \$retCode, 100);
    $function->bind_param_inout(2, \$retMsg, 100);
    
#These are the optional stored procs.
if($procName eq "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1W001"){
    $procedure = "BEGIN "."$procName"."(?, ?, ?, ?); "."END;";
    $function = $dbh->prepare($procedure);
    $function->bind_param_inout(3,$finRepId,SQL_VARCHAR);
    $function->bind_param_inout(4,$showPendingPolicies,SQL_VARCHAR);
}

# so if there's a cycle date, we start all over again?

if($cycleDate ne ""){
    $procedure = "BEGIN "."$procName"."(?, ?); "."END;";
    $function = $dbh->prepare($procedure);

    # there's nothing bound to param 1!!!

    $function->bind_param_inout(2, $cycleDate,SQL_VARCHAR);
    }

# why have this here?  it doesn't do anything?

if($procName eq "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1D007"){
    }

# make this part of an 'elsif'

if($procName eq "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1M005"){
    if($fromDate ne ""){
        $procedure = "BEGIN "."$procName"."(?, ?, ?); "."END;";
        $function = $dbh->prepare($procedure);
        $function->bind_param_inout(3,$fromDate,SQL_VARCHAR);
    }
    if($toDate ne ""){
        $procedure = "BEGIN "."$procName"."(?, ?, ?); "."END;";
        $function = $dbh->prepare($procedure);
        $function->bind_param_inout(3,$toDate,SQL_VARCHAR);
    }
    
}


print (scalar(localtime), " Proc Start", "\n");
$function->execute;

print (scalar(localtime), " Proc End", "\n");

print ("ReturnCode : ".$retCode, "\n");

print ("ReturnMsg : ".$retMsg, "\n");

if($retCode ne 0){
    print("Proc failed. Return code: ".$retCode);
    exit(-1);
}

   $dbh->disconnect;
[download]