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

On 17/01/2013 19:51, John Scoles wrote:
>
>
> > Date: Thu, 17 Jan 2013 19:47:06 +0000
> > From: [email protected]
> > To: [email protected]
> > CC: [email protected]; [email protected]
> > 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: [email protected]
> > > > To: [email protected]
> > > > CC: [email protected]
> > > > 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.
> Cheers
> John
>
Committed now with test case.

Martin