On 17/01/2013 19:51, John Scoles wrote:
> 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.
Cheers
John
Committed now with test case.
Martin
