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

On 15/01/13 16:01, Martin J. Evans wrote:
> On 15/01/13 15:04, Charles Jardine wrote:
>>
>> 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.

In this case the REF CURSOR variable in question is explicitly null.
I would expect the value returned via OCI to be accompanied by an
indicator variable with a value of -1,indicating a null value.
If this is the case, the value of the output variable is,
to quote the OCI manual, 'unchanged'. It should be ignored.

I am suggesting that the indicator variable should be tested before
the looking at the value. If indicator is -1, the value could be
the cursor returned by a previous execution of the same statement
handle.

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