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

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