> Date: Thu, 17 Jan 2013 13:48:15 +0000
> From: martin.ev...@easysoft.com
> To: c...@cam.ac.uk
> CC: dbi-dev@perl.org
> Subject: 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.
> Just my 2p on some verbage. I think 'null' would be the 'wrong' word to use
> here when refering to a this type of 'SQLT_RSET' . This type is a
> referance so it would either be in one of two states 'initialized' or
> 'unitiliazied' points to something or doesn't, and even when it doesn't
> point to somthing is still takes up memory!! You have to love 'C' ;) From my
> deailing with OCI what I would expect to get is an 'itilaized' referance no
> matter what state the object the ref is pointing to? I wish I had some time
> to play with this one as it is a goodie. Maybe tonight:) Did you check this
> in Martin or is it on a branch someplace?? Cheers
> 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
