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
