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

On 15/01/13 11:20, Martin J. Evans wrote:
> 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.

[snip]

>>
>> 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.

When a variable of a REF CURSOR type is declared is is initially
atomically null. It does not refer to cursor until it is OPENed,
or set to a non-null value in some other way.

Are you saying that, in the case of a NULL variable, the indicator
variable does not indicate nullity?

-- 
Charles Jardine - Computing Service, University of Cambridge
[email protected]    Tel: +44 1223 334506, Fax: +44 1223 334679