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

On 15/01/13 15:04, Charles Jardine wrote:
> 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?

I am saying when you have:

procedure fred(pcur OUT SYS_REFCURSOR) as
begin
pcur := NULL;
end;

then the output parameter DBD::Oracle sees appears to be a valid Oracle statement as you can call OCIAttrGet for OCI_ATTR_STMT_STATE and it works. The following snippet illustrates this:

    OCIAttrGet_stmhp_stat2(imp_sth, (OCIStmt*)phs->desc_h, &stmt_state, 0,
                                OCI_ATTR_STMT_STATE, status);

The desc_h in the parameters must be an OCIStmt * or surely this would not work.

Martin


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