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

On 17/01/13 12:26, Charles Jardine wrote:
> On 15/01/13 23:21, Martin J. Evans wrote:
>
>> I see loads of code setting indp so I created an indp2 in the phs and
>> passed it to OCIBindByName above and it is always 0 (Oracle assigned an
>> intact value to the host variable) whether a null cursor is returned or
>> not. It also did not seem to trigger ORA-01001 (invalid cursor) errors.
>> Also the test suite works with the indp set in the OCIBindByName. What a
>> PITA. I really wish when people write code like this they comment why
>> better.
>>
>> So it would seem resurrecting the phs->indp in the OCIBindByName does
>> not currently give me -1. Starting to wish I never started this. My
>> current change is better (in that it at least works whereas the previous
>> code did not at all) but you seem to suggest it is incomplete and that
>> concerns me. However, I've not been able to see what you suggested
>> should happen. I've already proved this speeds our application up a lot
>> compared with having to put a daft select 1 from dual in to just make
>> DBD::Oracle work so it would be a shame to fall at the last hurdle. Any
>> other ideas Charles?
>>
>> Bear in mind I cannot be getting a value from a previous execute as my
>> test code only does one execute so perhaps when you bind a SYS_REFCURSOR
>> you get a valid stmt handle back even if it is not executed. It seems
>> this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE)
>> on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED.
>
> Martin,
>
> I have reproduced your results. If you supply an indp, Oracle does not
> ignore it. It explicitly sets it to 0, indicating a non-null value.
> It seems that OCI does not represent a null PL/SQL cursor reference
> as a normal null value. As you originally thought, it represents it
> with a statement handle which is not open. You were right - I was wrong.

My post was simply based on observation and not what the docs said. However, I'm comforted to know that you get the same results.

> I can't find any documentation of this special treatment of null values
> of type SQLT_RSET, but, in trawling through the OCI manual, and Oracle's
> knowledge base, I have found several examples of binds of type SQLT_RSET,
> none of which use indicator variables.

I think I'll undo the change which passes an indicator. I wish I knew what that comment meant by causes an error but so long as I don't need the indicator it is irrelevant.

> I think is is important that PL/SQL null cursor references should
> reach Perl as undefined values. In the light of the above, I think
> that what you have already done is probably the best which can be done.
> It will treat cursor references which have explicitly closed as if
> they were null, but I think this is acceptable.

Excellent. In addition someone else who reported a similar RT a while ago tested my patch and it fixed their problem too.

> I hope I haven't wasted too much if your time.

Not at all. I am always grateful for your input. It was a avenue worth exploring.
  
Thanks again.

I'll apply this patch later today. It will return undef for a non executed output cursor.

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