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

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.

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 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.

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

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