> Date: Thu, 17 Jan 2013 19:47:06 +0000
> From: martin.ev...@easysoft.com
> To: byter...@hotmail.com
> CC: c...@cam.ac.uk; dbi-dev@perl.org
> Subject: Re: Problem with procedures returning a SYS_REFCURSOR which is not
> open/executed - possible fixes
>
> On 17/01/2013 18:32, John Scoles wrote:
> >
> >
> > > 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' ;)
> >
> To be honest the cursor is uninitialised, executed or finished and in
> this case it it is uninitialised and useless i.e., you cannot fetch from
> from it. undef is the the only reasonable value to return to Perl. As
> for any structure allocated in C land it is still deallocated as it is
> in the descriptor, all my change does is:
>
> a) return undef if the cursor is unusable
> b) stop DBD::Oracle attempting to describe an unusable cursor and erroring
>
> > 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?
> >
> All I can say is it is uninitialised and Charles has found that also.
>
> > 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??
> The patch is earlier in this thread.
>
> If I get time tonight I'm going to check it in to the trunk because I
> believe it is a legitimate fix for an existing bug that if anyone else
> had hit they'd be as stymied as I am. In fact, someone else did hit it -
> see the RT I referenced earlier in the thread - their problem resulted
> in a segfault due to the destroy method attempting to fetch from an
> invalid cursor.
> That would be great Martin. Seems I will have a little time today after all
> to look at it. So far I do not see any issues with the solution you came up
> with. CheersJohn
> >
> > Cheers
> >
> Martin
>
> > > 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
>
