Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes

On 15/01/13 10:56, Charles Jardine wrote:
> 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.
>

Thanks for your comments Charles.

In effect I believe my second patch does what you describe. If you define an output SYS_REFCURSOR in a procedure but don't open it you still get a cursor back but it is only initialised and not executed. My second patch (the one I prefer) looks at the state of the cursor and if it is initialised but not executed it avoids creating a DBI sth and that output parameter is seen as undef in perl land.

If I've misunderstood you please put me right.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com