Perl / PL-SQL Procedure question--How do I handle proc parameters?

Factual Questions
1

I’m trying to come up with a Perl script which can accept the name of a PL/SQL stored proc, plus any needed parameters, and then log on to an Oracle database and execute the proc. I came up with a minimal test procedure just to see if I could successfully bind an IN-OUT parameter and read its value after the call, and that worked. However, there was a point where I had to hard-code the name of that parameter. My goal, however is to have just one Perl script that can handle all the different stored procs and parameter requirements that I need in my operation. So obviously I have to have a way of not hardcoding my parameter names in the script.

What happens now, when I try to bind non-hardcoded parameter names, is that I get an error message similar to the following:**
Can’t bind a reference (SCALAR(0x1401ca390)) at run_plsql_proc_DPSCALC.pl line 59.
Can’t bind unknown placeholder ‘:’ (’:’) at run_plsql_proc_DPSCALC.pl line 59.
**

One solution I’ve thought of is to have the single Perl script write another on the fly that would include whatever hardcoded information is needed, and call that. But is there an easier way to get around this? I’m very new to Perl, so I suspect my first impression may be wrong.

2

After much poking around I discovered that instead of using named placeholders, you can use numbered ones, which can be bound to the members of an array of parameters.

So if the program is invoked with the name of the PL/SQL procedure followed by its parameters, e.g.

perl_plsql_driver proc_name parm1 parm2 parm3

then in the code, we first shift off the proc_name:

** my $PROC = shift **;

then assign the remaining parameters to an array, and the length of the remaining input array to a scalar:

**
my @INNIES = @ARGV;
my $INNIE_COUNT=@INNIES;
**
we can prepare the SQL statement as follows:

**
my $statement = 'BEGIN ’ . $PROC ;
$statement .= “(”;
for ($i = 0; $i < $INNIE_COUNT; $i++){
$j=$i+1; #(Placeholders start at 1, not 0)
$statement .= “:”.$j.", “;
}
$statement = $statement.=”)";
#add the closing paren, or if we have an IN-OUT error code, #add its placeholder here
**
This produces a statement similar to
BEGIN EXEC proc_name (:1, :2,…:n); END;

After preparing it, **
$csr = $lda->prepare($statement) or die “Couldn’t prepare statement” . $lda->errstr;
**
We then bind the parameters like this:
**
#bind the IN parameters, contained in the array “@INNIES”

for ($i=0; $i< $INNIE_COUNT; $i++){
$j=$i + 1;
print "
Binding $j to $INNIES[$i]";
$csr->bind_param ($j, $INNIES[$i] );
}

**