Re: DBD::Oracle 11gr2 ORA-38909
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.
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. 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
Re: DBI package problem?
Here's a code example (attached). Extract it to some folder. Run the tests with Perl. Doesn't include an installer, so remember to include the library folder, FBI: $ perl -IFBI/ 00_test.pl message from XS core! $ perl -IFBI/ 01_test.pl another message from XS core! You can set the FBI_PUREPERL environment variable to force usage of XS, Perl or Automatic mode: $ export FBI_PUREPERL=0 $ perl -IFBI/ 00_test.pl message from XS core! $ export FBI_PUREPERL=1 $ perl -IFBI/ 00_test.pl message from XS core! $ export FBI_PUREPERL=2 $ perl -IFBI/ 00_test.pl message from PurePerl core! If the XS core fails to load for some reason (if you want to try you can put some random garbage in the XS module), a warning is displayed and the PurePerl core is used instead. fbi_example.tar.gz Description: GNU Zip compressed data
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
