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

[Martin J. Evans]

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