On Tue, Apr 6, 2010 at 4:51 AM, Martin Evans <martin.ev...@easysoft.com>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? 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.
>
> Well it should be in there along with the other ones I forgot to add;)
if one looks at the dbi spec
private_attribute_info <#___top>
Returns a reference to a hash whose keys are the names of driver-private
attributes available for the kind of handle (driver, database, statement)
that the method was called on.
The values should be undef. Meanings may be assigned to particular values in
future.
> 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.
>
>
as it should be since you are setting these why would you want to get their
values??
> > 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. Do you mean dbd_st_prepare in
> oci8.c.
>
> >
> > Thanks,
> > Scott
> >
> >
> >> An attribute in the prepare method?
> >>
> >> Thanks,
> >> Scott
> >
> >
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
Anyway as a solution to the 'OCI_BATCH_MODE' problem I will add it in as
like id did for
ora_ncs_buff_mtpl so one can set it at the ENV, connect and statement
level so I can cover of any situation that may require a solution
Look for it as ora_oci_batch and ORA_DBD_OCI_BATCH for the env
Cheers john Scoles
--
See Pythian's Alex Gorbachev, co-author of "Expert Oracle Practices" at NoCOUG
Spring Conference May 20th.
Details, interview & book chapter in the May NoCOUG Journal:
http://bit.ly/alexnocoug
