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