On 11/01/13 16:04, Martin J. Evans wrote:
I am using DBD::Oracle and calling a procedure which returns a reference cursor. However, sometimes the reference cursor is not opened and only the procedure knows this. The problem is if I call the procedure from DBD::Oracle and the cursor is not opened I get an Oracle error saying the cursor is not executed:test procedure: procedure p_n2(pcur OUT SYS_REFCURSOR) AS begin pcur := NULL; end; example perl: my $s = $h->prepare(q/begin mypkg.p_n2(?); end;/); $s->bind_param_inout(1, \my $cursor, 100, {ora_type => ORA_RSET}); $s->execute; # errors The error occurs because DBD::Oracle attempts to call dbd_describe on the returned cursor (before perl land even sees it) and that code does things like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows if the statement is not executed. An easy solution is to just open an empty cursor if the procedure cannot open a real one by doing something like: open pcur for select 1 from dual; but I don't like that as DBD::Oracle will make dozens of calls and do quite a bit of work in dbd_describe which is wasting time and the purpose of the change to my procedure is to speed this application up not slow it down.
Martin, I agree that you have found a bug which ought to be fixed. However I think that the bug is much more fundamental, and much simpler than your analysis suggests. The DBI convention for database null values is to represent them by Perl undefined values. There is no reason why this convention should not apply to result sets. The perl code needs to test the indicator variable associated with the supposed result set. If this variable indicates a null value, all processing of actual value returned by Oracle should be skipped, as with any other null value. The pre-created perl statement handle should be left alone. It may me used in a subsequent execute of the same statement with different bind values. The value returned to perl should be undef, not a reference to the magic statement handle. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
