Passing arbitrary number of parameters to an Oracle function in perl

Question

Take an existing piece of perl code which calls an Oracle function with two params;

my $func = $dbh->prepare
(q
    {
     BEGIN
      :result := myStoredProc(value1 => :Param1, value2 => :Param2);
     END;
    }      
);
$func->bind_param(":Param1", $opt_a);
$func->bind_param(":Param2", $opt_b);
$func->bind_param_inout(":result", \$result, 20);
$func->execute();

If I now want to extend this functionality to allow any stored procedure to be called (with the name being passed as a parameter to the perl script I suppose).

Is it then possible to pass an arbitrary number of parameters to the function call?

Perl is by no means my strong point, so I've no idea how difficult a problem this is.

The part that I think presents the problem here is the actual SQL;

BEGIN
    :result := myStoredProc(value1 => :Param1, value2 => :Param2);
END;

I'm not really sure how this code could be adapted to take any number of params.

If anyone has any experience with this, I'd really appreciate any help you could give.

Thanks

Answer 1

What you pass to $dbh->prepare is a string, so you can dynamically adjust the string to call whatever Oracle stored procedure you want, providing as many or as little parameters that are required. You call bind_param for each parameter that you put into the string, when you created it dynamically. So, yes it is quite possible. Focus on writing a perl sub routine that will create the string you require

For instance: my $procString = &mysub( 'someOraclProc', 3 );

would produce a string with 3 parameters: { BEGIN :result := someOracleProc(value1 => :Param1, value2 => :Param2, value3 => :Param3); END; }

Then feed that string to the prepare method and call bind_parameter 3 times.

Answer 2

I've done this with unnamed parameters before, with the query having something like:

join(", ", map { "?" } @params)

And then passing @params to execute.

This doesn't work if you have to actually name the parameters, but you can build the query string using something similar, and then call bind_parameter for each.

Answer 3

Something like this would work:

sub callStoredFunction
{
    my ($funcName, @parameters) = @_;

    my $paramStr = join(', ',
        map { "value$_ => Param$_" }
            (1 .. scalar(@parameters));

    my $func = $dbh->prepare(sprintf("\n{\nBEGIN\n:result := $funcName( %s );\nEND;", $paramStr));

    for my $paramNum (1 .. scalar(@parameters))
    {
        $func->bind_param(":Param${paramNum}", $parameters[$paramNum]);
    }

    my $result;
    $func->bind_param_inout(":result", \$result, 20);
    $func->execute();
    return $result;
}