Re^4: DBI bind inout problem

(Have now registered with this excellent site). 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!

Comment on Re^4: DBI bind inout problem

Replies are listed 'Best First'.
Re^5: DBI bind inout problem by olus (Curate) on Dec 05, 2006 at 10:21 UTC
Right. So what you now need to do is to check for errors when preparing and executing your sql statement. If your code if breaking you are not notified because of the eval block. Modify the prepare and execute statements to look something like this `my $func = $dbh->prepare( ... }) or die DBI->errstr; ... $func->execute or die DBI->errstr;` [download] This will tell you if there is any problem when executing that code, namely if the user that is connecting to the database is granted the required permissions to do what you want it to.	[reply] [d/l]
Re^6: DBI bind inout problem by Liche (Initiate) on Dec 05, 2006 at 13:46 UTC
Thanks for your help. I have found the problem, and it is nothing like what I thought it was. The problem lies with uncommitted data in other schemas being ignored by the view, but not by direct access, thus causing the cursor to point to null data, which are then put into the bound variable. Thanks again.	[reply]