It's hard to say, without knowing what your procs are doing. You're basically doing some crude polymorphism here (kinda, sorta). Quite possibly this will all be easier to handle directly in PL/SQL with an overriden "wrapper" proc.

But if you must use a Perl wrapper, you need to keep in mind that you can't "re-prepare" a statement, which is what looks like you're trying to do. That is, if you're going to run just one proc, you should prepare and execute just once. For example, the second part of this:

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);
#
# ... <snip>
#
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);
[download]

Basically, you need to arrange your code to dispatch to a single prepare and execute based on the name it's passed. You can either do this with a hash or a dispatch structure, like one of the ones in perlsyn, the section labelled "Basic BLOCKs and switch statements".

Again, as I mentioned, the best may be to do all in PL/SQL, but it's hard to say without seeing the procs.

Thanks for your help. The problem is that there are 100's of stored procs called through VBScript to be run through scheduler and they fail every other day. So I am to get the same thing done through perl script which will be then put on the scheduler. This being the reason I am doing it in perl. Now as you have mentioned Hash...how will that be useful. Do you mean to say that I should declare a hash and depending on the arguments passed those arguments will get assigned as values to the respective keys. If that done then how will that change the database execution part. Please advice. I am in the learning phase of perl and so the small silly questions.

The basic idea is this: in your code example above, you're making decisions on what data to pass to and from the procedures based on their names. But you need to factor out the actual call(s) to prepare and execute so you can do those just once. So, you want to capture all the name-to-parameter association information in a hash, and use that to drive the code. That will also make it easier to add new cases in the future. This strategy depends on the how regular the calls are; if there's not a lot of regularity, you may want to move to a dispatch tree. I'll try to show you examples of both.

Here's a possible way to do it with a hash (untested, this is just to give you the idea):

my %dispatch = (
# Key = name of proc
# Value = array of arguments to pass
    "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1D007" =>
        [ \$retcode, \$retmsg ],
    "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1W001" =>
        [ \$retcode, \$retmsg, \$finRepId, \$showPendingPolicies ],
# and so on
);
# Later...
my $args = $dispatch{$procName};
my $procedure =
    "BEGIN $procName( "
    . join(',', (('?') x @$args))
    # that makes a comma-separated list of '?'s,
    # the same number as there are elements in $args
    . "); END;";
my $function = $dbh->prepare($procedure);
my $argc = 1;
for my $arg (@$args)
{
    $function->bind_param($argc++, $arg, SQL_VARCHAR);
}
$function->execute;
[download]

That's not exactly equivalent to your code -- I'm having a hard time figuring out the difference between what you want and what you wrote -- but you can see the intent.

If you have so much irregularity that the hash can't accommodate everything easily, then set up a dispatch, something like this:

my ($procedure, $function);
DISPATCH: {
    if ($procName eq "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1D007")
    {
        $function = $dbh->prepare("BEGIN $procName(?, ?); END;");
        $function->bind_param(1, \$retcode, 100); #? 100?
        $function->bind_param(2, \$retmsg, 100);
        last DISPATCH;
    }
    if ($procName eq "PAR_REPORTS_POS.SUN_PRC_AT_I_POSM1W001")
    (
        $function = $dbh->prepare("BEGIN $procName(?, ?, ?, ?); END;")
+;
        $function->bind_param(1, \$retcode, 100); #? 100?
        $function->bind_param(2, \$retmsg, 100);
        $function->bind_param(3, \$finRepId, SQL_VARCHAR);
        $function->bind_param(4, \$showPendingPolicies, SQL_VARCHAR);
        last DISPATCH;
    }
    # and so on...
} #close block labelled DISPATCH;
$function->execute;
[download]

Again, not exactly equivalent to your code, but I think you can see the intended structure. Spend some time with perlsyn if you're uncomfortable with how that second example works.

Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.