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

On 15/01/2013 17:52, Charles Jardine wrote:
> 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.
>
Thanks Charles.

I think your explanation means DBD::Oracle is even more broken than I 
thought wrt to output cursors.

Looks like I need to do a bit more reading. Thanks for the pointer.

Out of interest I looked at the code a little more and see the 
descriptor in the D::O's phs is freed and reallocated pre-execute. The 
parameter is also rebound. In pp_exec_rset it does (note my comment 
labelled MJE):

     if (pre_exec) {    /* pre-execute - allocate a statement handle - 
MJE it does not do this now */
         sword status;

         /* extproc deallocates everything for us */
         if (is_extproc)
             return 1;

         /* MJE what is the following test supposed to be - always true? */
         if (!phs->desc_h || 1) { /* XXX phs->desc_t != OCI_HTYPE_STMT) */
             if (phs->desc_h) {
                 OCIHandleFree_log_stat(imp_sth, phs->desc_h, 
phs->desc_t, status);
                 phs->desc_h = NULL;
             }
             phs->desc_t = OCI_HTYPE_STMT;
             OCIHandleAlloc_ok(imp_sth, imp_sth->envhp, &phs->desc_h, 
phs->desc_t, status);
          }

         phs->progv = (char*)&phs->desc_h;
         phs->maxlen = 0;

         OCIBindByName_log_stat(imp_sth, imp_sth->stmhp, &phs->bndhp, 
imp_sth->errhp,
             (text*)phs->name,
             (sb4)strlen(phs->name),
             phs->progv,
             0,
             (ub2)phs->ftype,
             NULL, /* using &phs->indp triggers ORA-01001 errors! */
             NULL,
             0,
             0,
             NULL,
             OCI_DEFAULT,
             status);

However, as you said the phs->indp is -1 as you said. I will correct my 
change and make it check indp first.

As you know there is a lot of OCI code in DBD::Oracle and I'm not 
familiar with it all by a long way so I'm always grateful for any 
pointers/help.

Martin