Have spent ages trying to squash a tricky bug, and am still stuck. Please help!
I'm writing a series of wrapper functions in Perl for some stored procedures in a large Oracle 10 database. I'm using essentially the same code to retrieve the return values of different procedures, and it works for some (returning an integer) but not others (returning undef). I've called the PL/SQL code directly in the database and it behaves as expected; assuming that the problem is not in the database, what might cause this problem?
Code:
sub Create($$$) {
my $dbh = shift(@_);
my @parameters = @_;
my $return_value;
eval {
my $func = $dbh->prepare(q{
DECLARE
returned_cursor foo_CURSOR;
returned_row foo_view%ROWTYPE;
BEGIN
returned_cursor := foo.fn( :parameter1, );
FETCH returned_cursor INTO returned_row;
:id := returned_row.id; END; });
$func->bind_param(":parameter1", $parameters[1]);
$func->bind_param_inout(":id", \$return_value, 1);
$func->execute;
};
<error catching>
}
Basically it calls the stored procedure fn in the schema foo, which creates a row in foo, and returns a cursor to a row in foo_view. I then want to return the id from this row. $return_value is undef for one use of this block, but the correct if for another (if I define a value for this before executing the PL/SQL, it it overwritten with undef).
There are a total of four cases:
1) db-stored PL/SQL function returns integer, binding works
2) db-stored PL/SQL function returns cursor, binding works
3) db-stored PL/SQL function returns integer, binding doesn't work
4) db-stored PL/SQL function returns cursor, binding doesn't work
For the cursors I am then SELECTing the relevant id value INTO the bound variable. As far as I can tell, the only difference between the working and non-working cases is that the ones that don't work are binding to a value which lives in a different schema to the called function (i.e. a foreign key pointing to a different database), whereas those that work are in the same schema. Is the DBI getting lost in the redirection?
Have run the code on another machine, with various versions of Perl/DBI, including the latest. The functions are all definitely executing correctly. It very much looks to me like the problem lies with (or is manifested by) the binding.
Thanks for your help!
dBforums
>
Bind inout problem
12-05-06, 04:49
Linear Mode
