Re: DBD::Oracle 11gr2 & ORA-38909

On Tue, 2010-04-06 at 09:51 +0100, Martin Evans wrote:
> I haven't seen a reply to this yet but I've been on holiday so might
> have missed it:
> 
> Scott T. Hildreth wrote:
> > On Wed, 2010-03-31 at 12:20 -0500, Scott T. Hildreth wrote:
> >> We have run into an issue with array processing in 11g.  The developer
> >> was using execute_array and his sql statement had 'LOG ERRORS' in it.
> >> This did not error out until we switched to 11g.  The issue is that only
> >> one is allowed, either 'LOG ERRORS' or 'SAVE EXCEPTIONS'.  Our DBA
> >> logged and error report with Oracle and after several posts back and
> >> forth this is what they concluded,
> >>
> >> ======================================================================
> >> After investigation and discussion, development has closed the bug as
> >> 'Not a Bug' with the following reason:
> >>
> >> "this is an expected behavior in 11g and the user needs to specify
> >> either of 'SAVE EXCEPTIONS' clause or the 'DML error logging', but NOT
> >> both together.
> >> The batch error mode, in the context of this bug, is basically referring
> >> to the SAVE EXCEPTIONS clause.
> >> It seems the code is trying to use both dml error logging and batch
> >> error handling for the same insert. In that case, this is not a bug.
> >>
> >> For INSERT, the data errors are logged in an error logging table (when
> >> the dml error logging feature is used) or returned in batch error
> >> handles (when using batch mode).
> >> Since the error messages are available to the user in either case, there
> >> is no need to both log the error in the error logging table and return
> >> the errors in batch error handles, 
> >> and we require the user to specify one option or the other but not both
> >> in 11G.
> >>
> >> Both features exist in 10.x. For 11.x, users should change their
> >> application to avoid the error.
> >> ======================================================================
> >>
> >> So basically we need a way to turn off the 'SAVE EXCEPTIONS' for the
> >> batch mode.  I found in dbdimp.c that the oci_mode is being set to 
> >> OCI_BATCH_ERRORS in the ora_st_execute_array function.  I was planning 
> >> on setting it to OCI_BATCH_MODE and running a test to see if this will
> >> not error out.  I report back when I have run the test, but I was
> >> wondering what would be the best way to give the user the ability to
> >> override the oci_mode. 
> > 
> > Setting oci_mode to OCI_BATCH_MODE works.  So I want to add a prepare
> > attribute that will turn off the SAVE EXCEPTIONS.  I'm looking for some 
> > direction on how to add it to dbdimp.c. I haven't thought of a name yet,
> > but something like 
> > 
> > my $sth = $dbh->prepare($SQL,{ora_oci_err_mode => 0});
> > 
> > I assume I would have to add it to dbd_db_FETCH_attrib() and would I do
> > something like this in ora_st_execute_array(),
> 
> Don't you mean dbd_st_FETCH_attrib as it is a statement level attribute
> not a connection one?

Yes.


>  Anyway, I don't think it is required unless you
> really want to get it back out in a Perl script.
> 
> I don't even think you need to add it to a statements
> private_attribute_info but then when I checked Oracle.pm it appears a
> load of prepare flags have been added. I might be wrong here but since
> there is no way to get ora_parse_lang etc (prepare attributes) I don't
> think they should be in private_attribute_info.
> 
> perl -e 'use DBI;$h =
> DBI->connect("dbi:Oracle:host=xxx;sid=yyy","xxx","yyy"); $s =
> $h->prepare("select 1 from dual", {ora_parse_lang => 2}); print
> $s->{ora_parse_lang};'
> 
> prints nothing as you'd expect as there is no way to get ora_parse_lang.
> 
> >         if (DBD_ATTRIB_TRUE(attr,"ora_oci_err_mode",16,svp))
> >                 DBD_ATTRIB_GET_IV(  attr, "ora_oci_err_mode",  16, svp, ora_oci_err_mode);
> 
> I don't understand why you need it in ora_st_execute_array - the
> statement has already been parsed by then.

I don't either, I was looking at other attributes and how they are in
the code.  That's why I asked for direction, :-)

>  Do you mean dbd_st_prepare in
> oci8.c.


I think John is going to add this attribute, but I will give it a whirl
for the sake of learning more about DBD::Oracle.

Thanks.
> 
> > 
> > Thanks,
> > Scott
> > 
> > 
> >>  An attribute in the prepare method?  
> >>
> >> Thanks,
> >> Scott
> > 
> > 
> 
> Martin