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