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.

In reply to Re^3: DBI Execute problem by VSarkiss
in thread DBI Execute problem by Nesh

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.