Re: DBD::Oracle 11gr2 ORA-38909
Had a quick look at it today give it a try agian but this time make sure you
have autocommit off
AutoCommit=1
on the connection method
cheers
On Fri, May 21, 2010 at 11:20 AM, John Scoles sco...@pythian.com wrote:
Ok I guess it is back to square 1 on this.
Unfortunetly no time to look at it today
Will write up a small test script and see if my patch actually does
anything
You may hear from me in a few days
Cheers
John
Scott T. Hildreth wrote:
On Fri, 2010-05-21 at 09:23 +0100, Martin Evans wrote:
John Scoles wrote:
Ok I have patched up a solution I think will work across the board and
you
can find it here
http://svn.perl.org/modules/dbd-oracle/branches/oci_batch
here are the details
ora_oci_batch
For 11g users you may encounter an error while using the execute_array
in
that it does not
return a full list of tuples. This seems to be a result in that a
statement
can only
have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a
value
should stop this
problem error.
For convenience I have added support for a 'ORA_DBD_OCI_BATCH'
environment variable that you can use at the OS level to set this
value. It can also be set as an attribute on both the Connect and
Prepare.
Unfortunately I can't test it (do not have an 11g box yet) so It will
stay
in the above branch until it is tested hopefully by you Scott
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
I'm not sure why I seem to have ignored your mail but I just noticed it
again - sorry for the delay.
I checked out the branch you mentioned and
export ORA_DBD_OCI_BATCH=1
but 26exe_array still seems to fail for me:
Sorry John, I meant to test earlier but its been a busy week.
Fails for me as well.
DB7 x $dbh-{'ora_oci_batch'}
0 1
DB8 n
main::(../tst_exec_ary.pl:13): my $sth = $dbh-prepare(Insert into
TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY);
DB8 n
main::(../tst_exec_ary.pl:15): $sth-bind_param_array(1, [ qw(One Uno Il
oNe) ]);
DB8 n
main::(../tst_exec_ary.pl:16): $sth-bind_param_array(2, [ 2, 22, 0, 222
]);
DB8 n
main::(../tst_exec_ary.pl:17): $sth-bind_param_array(3, [ qw(20070101
20080101 20090101 20060101) ]);
DB8 n
main::(../tst_exec_ary.pl:21): $sth-execute_array({});
DB8 n
DBD::Oracle::st execute_array failed: ORA-38909: DML Error logging is not
supported with BATCH ERROR mode (DBD ERROR: OCIStmtExecute) [for Statement
Insert into TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY] at
../tst_exec_ary.pl line 21.
at ../tst_exec_ary.pl line 21
mar...@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb
t/26exe_array.t
t/26exe_array.t ..
1..17
ok 1 - use DBI;
ok 2 - The object isa DBI::db
ok 3 - ... execute_array should return true
ok 4 - ... we should have 10 tuple_status
ok 5 - ... execute_array should return false
ok 6 - ... we should have 10 tuple_status
ok 7 - ... we should get text
ok 8 - ... we should get -1
ok 9 - ... we should get a warning
ok 10 - ... execute_for_fetch should return true
not ok 11 - ... we should have 19 tuple_status
# Failed test '... we should have 19 tuple_status'
# at t/26exe_array.t line 128.
# got: 10
# expected: 19
ok 12 - ... execute_array should return flase
ok 13 - ... we should have 10 tuple_status
not ok 14 - ... we should have 48 rows
# Failed test '... we should have 48 rows'
# at t/26exe_array.t line 154.
# got: 30
# expected: 48
ok 15 - ... execute_array should return true
ok 16 - ... \#5 should be a warning
ok 17 - ... we should have 10 tuple_status
# Looks like you failed 2 tests of 17.
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/17 subtests
Test Summary Report
---
t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2)
Failed tests: 11, 14
Non-zero exit status: 2
Files=1, Tests=17, 0 wallclock secs ( 0.02 usr 0.01 sys + 0.05 cusr
0.01 csys = 0.09 CPU)
Result: FAIL
This was using oracle 11.1 server and 11.1 instant client.
If I've not set the right thing let me know.
Martin
--
Catch Alex Sheeri at ODTUG/Kaleidoscope - June 27 - July 1.
Hear Sheeri speak or email eve...@pythian.com to meet with Pythian.
Re: DBD::Oracle 11gr2 ORA-38909
John Scoles wrote: Ok I have patched up a solution I think will work across the board and you can find it here http://svn.perl.org/modules/dbd-oracle/branches/oci_batch here are the details ora_oci_batch For 11g users you may encounter an error while using the execute_array in that it does not return a full list of tuples. This seems to be a result in that a statement can only have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value should stop this problem error. For convenience I have added support for a 'ORA_DBD_OCI_BATCH' environment variable that you can use at the OS level to set this value. It can also be set as an attribute on both the Connect and Prepare. Unfortunately I can't test it (do not have an 11g box yet) so It will stay in the above branch until it is tested hopefully by you Scott 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 I'm not sure why I seem to have ignored your mail but I just noticed it again - sorry for the delay. I checked out the branch you mentioned and export ORA_DBD_OCI_BATCH=1 but 26exe_array still seems to fail for me: mar...@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb t/26exe_array.t t/26exe_array.t .. 1..17 ok 1 - use DBI; ok 2 - The object isa DBI::db ok 3 - ... execute_array should return true ok 4 - ... we should have 10 tuple_status ok 5 - ... execute_array should return false ok 6 - ... we should have 10 tuple_status ok 7 - ... we should get text ok 8 - ... we should get -1 ok 9 - ... we should get a warning ok 10 - ... execute_for_fetch should return true not ok 11 - ... we should have 19 tuple_status # Failed test '... we should have 19 tuple_status' # at t/26exe_array.t line 128. # got: 10 # expected: 19 ok 12 - ... execute_array should return flase ok 13 - ... we should have 10 tuple_status not ok 14 - ... we should have 48 rows # Failed test '... we should have 48 rows' # at t/26exe_array.t line 154. # got: 30 # expected: 48 ok 15 - ... execute_array should return true ok 16 - ... \#5 should be a warning ok 17 - ... we should have 10 tuple_status # Looks like you failed 2 tests of 17. Dubious, test returned 2 (wstat 512, 0x200) Failed 2/17 subtests Test Summary Report --- t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2) Failed tests: 11, 14 Non-zero exit status: 2 Files=1, Tests=17, 0 wallclock secs ( 0.02 usr 0.01 sys + 0.05 cusr 0.01 csys = 0.09 CPU) Result: FAIL This was using oracle 11.1 server and 11.1 instant client. If I've not set the right thing let me know. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: DBD::Oracle 11gr2 ORA-38909
On Fri, 2010-05-21 at 09:23 +0100, Martin Evans wrote:
John Scoles wrote:
Ok I have patched up a solution I think will work across the board and you
can find it here
http://svn.perl.org/modules/dbd-oracle/branches/oci_batch
here are the details
ora_oci_batch
For 11g users you may encounter an error while using the execute_array in
that it does not
return a full list of tuples. This seems to be a result in that a statement
can only
have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value
should stop this
problem error.
For convenience I have added support for a 'ORA_DBD_OCI_BATCH'
environment variable that you can use at the OS level to set this
value. It can also be set as an attribute on both the Connect and Prepare.
Unfortunately I can't test it (do not have an 11g box yet) so It will stay
in the above branch until it is tested hopefully by you Scott
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
I'm not sure why I seem to have ignored your mail but I just noticed it
again - sorry for the delay.
I checked out the branch you mentioned and
export ORA_DBD_OCI_BATCH=1
but 26exe_array still seems to fail for me:
Sorry John, I meant to test earlier but its been a busy week.
Fails for me as well.
DB7 x $dbh-{'ora_oci_batch'}
0 1
DB8 n
main::(../tst_exec_ary.pl:13): my $sth = $dbh-prepare(Insert into TestArray
Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY);
DB8 n
main::(../tst_exec_ary.pl:15): $sth-bind_param_array(1, [ qw(One Uno Il oNe)
]);
DB8 n
main::(../tst_exec_ary.pl:16): $sth-bind_param_array(2, [ 2, 22, 0, 222 ]);
DB8 n
main::(../tst_exec_ary.pl:17): $sth-bind_param_array(3, [ qw(20070101
20080101 20090101 20060101) ]);
DB8 n
main::(../tst_exec_ary.pl:21): $sth-execute_array({});
DB8 n
DBD::Oracle::st execute_array failed: ORA-38909: DML Error logging is not
supported with BATCH ERROR mode (DBD ERROR: OCIStmtExecute) [for Statement
Insert into TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY] at
../tst_exec_ary.pl line 21.
at ../tst_exec_ary.pl line 21
mar...@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb t/26exe_array.t
t/26exe_array.t ..
1..17
ok 1 - use DBI;
ok 2 - The object isa DBI::db
ok 3 - ... execute_array should return true
ok 4 - ... we should have 10 tuple_status
ok 5 - ... execute_array should return false
ok 6 - ... we should have 10 tuple_status
ok 7 - ... we should get text
ok 8 - ... we should get -1
ok 9 - ... we should get a warning
ok 10 - ... execute_for_fetch should return true
not ok 11 - ... we should have 19 tuple_status
# Failed test '... we should have 19 tuple_status'
# at t/26exe_array.t line 128.
# got: 10
# expected: 19
ok 12 - ... execute_array should return flase
ok 13 - ... we should have 10 tuple_status
not ok 14 - ... we should have 48 rows
# Failed test '... we should have 48 rows'
# at t/26exe_array.t line 154.
# got: 30
# expected: 48
ok 15 - ... execute_array should return true
ok 16 - ... \#5 should be a warning
ok 17 - ... we should have 10 tuple_status
# Looks like you failed 2 tests of 17.
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/17 subtests
Test Summary Report
---
t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2)
Failed tests: 11, 14
Non-zero exit status: 2
Files=1, Tests=17, 0 wallclock secs ( 0.02 usr 0.01 sys + 0.05 cusr
0.01 csys = 0.09 CPU)
Result: FAIL
This was using oracle 11.1 server and 11.1 instant client.
If I've not set the right thing let me know.
Martin
Re: DBD::Oracle 11gr2 ORA-38909
Ok I guess it is back to square 1 on this.
Unfortunetly no time to look at it today
Will write up a small test script and see if my patch actually does anything
You may hear from me in a few days
Cheers
John
Scott T. Hildreth wrote:
On Fri, 2010-05-21 at 09:23 +0100, Martin Evans wrote:
John Scoles wrote:
Ok I have patched up a solution I think will work across the board and you
can find it here
http://svn.perl.org/modules/dbd-oracle/branches/oci_batch
here are the details
ora_oci_batch
For 11g users you may encounter an error while using the execute_array in
that it does not
return a full list of tuples. This seems to be a result in that a statement
can only
have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value
should stop this
problem error.
For convenience I have added support for a 'ORA_DBD_OCI_BATCH'
environment variable that you can use at the OS level to set this
value. It can also be set as an attribute on both the Connect and Prepare.
Unfortunately I can't test it (do not have an 11g box yet) so It will stay
in the above branch until it is tested hopefully by you Scott
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
I'm not sure why I seem to have ignored your mail but I just noticed it
again - sorry for the delay.
I checked out the branch you mentioned and
export ORA_DBD_OCI_BATCH=1
but 26exe_array still seems to fail for me:
Sorry John, I meant to test earlier but its been a busy week.
Fails for me as well.
DB7 x $dbh-{'ora_oci_batch'}
0 1
DB8 n
main::(../tst_exec_ary.pl:13): my $sth = $dbh-prepare(Insert into TestArray
Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY);
DB8 n
main::(../tst_exec_ary.pl:15): $sth-bind_param_array(1, [ qw(One Uno Il oNe)
]);
DB8 n
main::(../tst_exec_ary.pl:16): $sth-bind_param_array(2, [ 2, 22, 0, 222 ]);
DB8 n
main::(../tst_exec_ary.pl:17): $sth-bind_param_array(3, [ qw(20070101
20080101 20090101 20060101) ]);
DB8 n
main::(../tst_exec_ary.pl:21): $sth-execute_array({});
DB8 n
DBD::Oracle::st execute_array failed: ORA-38909: DML Error logging is not supported with
BATCH ERROR mode (DBD ERROR: OCIStmtExecute) [for Statement Insert into TestArray
Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY] at ../tst_exec_ary.pl line 21.
at ../tst_exec_ary.pl line 21
mar...@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb t/26exe_array.t
t/26exe_array.t ..
1..17
ok 1 - use DBI;
ok 2 - The object isa DBI::db
ok 3 - ... execute_array should return true
ok 4 - ... we should have 10 tuple_status
ok 5 - ... execute_array should return false
ok 6 - ... we should have 10 tuple_status
ok 7 - ... we should get text
ok 8 - ... we should get -1
ok 9 - ... we should get a warning
ok 10 - ... execute_for_fetch should return true
not ok 11 - ... we should have 19 tuple_status
# Failed test '... we should have 19 tuple_status'
# at t/26exe_array.t line 128.
# got: 10
# expected: 19
ok 12 - ... execute_array should return flase
ok 13 - ... we should have 10 tuple_status
not ok 14 - ... we should have 48 rows
# Failed test '... we should have 48 rows'
# at t/26exe_array.t line 154.
# got: 30
# expected: 48
ok 15 - ... execute_array should return true
ok 16 - ... \#5 should be a warning
ok 17 - ... we should have 10 tuple_status
# Looks like you failed 2 tests of 17.
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/17 subtests
Test Summary Report
---
t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2)
Failed tests: 11, 14
Non-zero exit status: 2
Files=1, Tests=17, 0 wallclock secs ( 0.02 usr 0.01 sys + 0.05 cusr
0.01 csys = 0.09 CPU)
Result: FAIL
This was using oracle 11.1 server and 11.1 instant client.
If I've not set the right thing let me know.
Martin
Re: Clarification sought on private_attribute_info WAS Re: DBD::Oracle 11gr2 ORA-38909
[Sorry for the delay]
On the one hand, the DBI makes a distinction between method attributes
and handle attributes. They're different things.
On the other hand, the handle constructor methods are a special case.
The method attributes passed to connect() are applied to the newly
created dbh as handle attributes.
The same _ought_ to be true of prepare() but isn't, sadly. This is one
of those areas of the DBI where the risk of breakage now far outweighs
the gains from trying to fix it.
So, to answer your question Martin, I'll update the docs to clarify that
it applies to handle attributes.
Tim.
On Fri, May 07, 2010 at 04:35:32PM +0100, Martin Evans wrote:
Apologies for top posting but this is an old thread and I include it for
reference but really I'd like some clarification from Tim as to whether
the following is correct.
As John states the DBI documentation says for private_attribute_info:
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.
My question is does this include attributes which may be specified on
the prepare call when there is no separate store/fetch on the handle. e.g.,
$h-prepare(select 1 from dual, {ora_parse_lang = 2});
You cannot set ora_parse_lang on a $sth or retrieve it so should it be
in private_attribute_info?
I would like to know since if ora_parse_lang in DBD::Oracle should be in
private_attribute_info when it cannot be independently stored or fetched
then this impacts similar prepare attributes in DBD::ODBC.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
John Scoles wrote:
On Tue, Apr 6, 2010 at 4:51 AM, Martin Evans martin.ev...@easysoft.com
mailto: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
Re: DBD::Oracle 11gr2 ORA-38909
On Tue, Apr 6, 2010 at 4:51 AM, Martin Evans martin.ev...@easysoft.comwrote:
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
Clarification sought on private_attribute_info WAS Re: DBD::Oracle 11gr2 ORA-38909
Apologies for top posting but this is an old thread and I include it for
reference but really I'd like some clarification from Tim as to whether
the following is correct.
As John states the DBI documentation says for private_attribute_info:
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.
My question is does this include attributes which may be specified on
the prepare call when there is no separate store/fetch on the handle. e.g.,
$h-prepare(select 1 from dual, {ora_parse_lang = 2});
You cannot set ora_parse_lang on a $sth or retrieve it so should it be
in private_attribute_info?
I would like to know since if ora_parse_lang in DBD::Oracle should be in
private_attribute_info when it cannot be independently stored or fetched
then this impacts similar prepare attributes in DBD::ODBC.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
John Scoles wrote:
On Tue, Apr 6, 2010 at 4:51 AM, Martin Evans martin.ev...@easysoft.com
mailto: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
Re: DBD::Oracle 11gr2 ORA-38909
Ok I have patched up a solution I think will work across the board and you can find it here http://svn.perl.org/modules/dbd-oracle/branches/oci_batch here are the details ora_oci_batch For 11g users you may encounter an error while using the execute_array in that it does not return a full list of tuples. This seems to be a result in that a statement can only have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value should stop this problem error. For convenience I have added support for a 'ORA_DBD_OCI_BATCH' environment variable that you can use at the OS level to set this value. It can also be set as an attribute on both the Connect and Prepare. Unfortunately I can't test it (do not have an 11g box yet) so It will stay in the above branch until it is tested hopefully by you Scott 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
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: 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
Re: DBD::Oracle 11gr2 ORA-38909
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(),
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);
Thanks,
Scott
An attribute in the prepare method?
Thanks,
Scott
DBD::Oracle 11gr2 ORA-38909
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. An attribute in the prepare method? Thanks, Scott
