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[Fre
+quency] -o[Fromdate] -o[ToDate] -o[FinRepId] -o[ShowPendingPolicies]"
+;
    exit();
}
print ("In the script\n");

# Open Database connection

$strConnectionString = &GetConnectionString;
$_=$strConnectionString;
if(/data source=([0-9A-Za-z]*;)user id=([0-9A-Za-z].+;)password=([0-9A
+-Za-z].+)/){
    $myDSN=$1;    $user=$2; $password=$3;
    chop($myDSN); chop($user);
}
  
    $dsn ="dbi:Oracle:$myDSN";
    $dbh = DBI->connect ($dsn, $user, $password, {
        PrintError =>1,
        RaiseError =>1
        }) or die "Database connection not made: $DBI::errstr"; 

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);
    }

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

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

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]