Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm writing a series of wrapper functions in Perl for some stored procedures in an Oracle 10 database. I'm using essentially the same code to retrieve the return values of two different procedures, and it works for one (returning an integer) but not the other (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?
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.

Replies are listed 'Best First'.
Re: DBI bind inout problem
by olus (Curate) on Dec 04, 2006 at 12:28 UTC
    Try checking if you're getting some error in the execution of your sql statements.
    ... FETCH returned_cursor INTO returned_row; :id := returned_row.id; EXCEPTION when others then :id := SQLERRM; END; }); $func->bind_param(":parameter1", $parameters[1]); $func->bind_param_inout(":id", \$return_value, 1000); $func->execute; }; ...

    Maybe you'll get an hint on what may be happening.

    -----
    olus
      Thanks for the hint, but it didn't throw anything. I'm pretty certain the SQL is executing correctly (by looking at the results in the database itself), but that there is some problem with the binding.
        Can you show the code for the two pl/sql functions and the calls you make to Create?
        Is the type of the return value the same for the two functions?
Re: DBI bind inout problem
by Liche (Initiate) on Dec 05, 2006 at 09:38 UTC
    (Have now registered). Spent all day yesterday trying to fix this and am still stuck. Help much appreciated! I'm not able post the PL/SQL functions (or reduced versions) as they are a) complicated, b) legacy and c) not for public consumption. Sorry! I'll elaborate as best I can. There are a total of four cases:

    1) PL/SQL function returns integer, binding works
    2) PL/SQL function returns cursor, binding works
    3) PL/SQL function returns integer, binding doesn't work
    4) 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. DB is Oracle 10. 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!