Beware execute_array with DBI/DBD::Oracle
1 February, 2011 - 19:23 — martinjevans
I've been using DBD::Oracle for a fairly major application here for some time. Most of the code is actually in Oracle procedures but some remaining code is in Perl. Since we upgraded to Oracle 11Gr2 DBD::Oracle's 26exex_array.t test has always failed. I've not been too bothered about this since we don't use the execute_array method (implemented in DBI for most drivers but implemented in DBD::Oracle directly). However, testing has unearthed some worrying results.
The DBI specification for execute_array (and implementation) is not mirrored in DBD::Oracle as of 1.27 and the discrepencies are really worrying. The major thing is that DBD::Oracle does not raise an error if a batch of row inserts/updates passed to execute_array fail (whether it be 1 or all of them). DBD::Oracle issues a warning instead (presumably because the OCI call returns SUCCESS_WITH_INFO). This means if you want to insert 5 rows into a table with execute_array and have RaiseError set then if one of them fails and you wrapped the execute_array in an eval it is tough luck - you won't know unless you also have a SIGWARN handler as well or check the execute_array return (which you won't because you had RaiseError set). Now this should not matter most of the time but if some of them fail then you have a problem as you'll expect the working inserts/updates to be committed (AutoCommit) or be committed when you call commit.
The main problem arises because some versions of Oracle database (the 11.2.0.2 I have) have a bug which seems to have been fixed in a later patch set but I've not located the patch yet; the bug stops the good rows from being committed.
The issue in DBD::Oracle is just as much a problem though as even if you have a good Oracle database if one of the tuples in the batch fails and RaiseError is set, an error is not raised. Instead a warning is raised and I'd guess a lot of people won't catch those. In fact, if you set PrintWarn in DBI off even the warning is not recieved. The following code illustrates the double problem:
For a broken Oracle database and DBD::Oracle 1.27 you'll get:
Even for a working Oracle database we get:
So, my advice:
use DBI;
use strict;
use Data::Dumper;
use warnings;
my ($captured_warning, $captured_error);
$SIG{__WARN__} = sub { $captured_warning = $_[0]; warn $_[0]};
sub error_handler
{
print "Error Handler called\n";
print Data::Dumper->Dump([\@_], [qw(captured_error_in_handler)]);
my ($msg, $handle, $val) = @_;
$captured_error = "$msg";
0; # pass errors on
}
my $dbh = DBI->connect(
'DBI:Oracle:host=betoracle.easysoft.local;sid=devel', 'bet', 'b3t',
{ RaiseError => 1, PrintError => 0,
#PrintWarn => 1,
#ora_chunk_array_size => 2,
HandleError => \&error_handler
});
my $v = $dbh->selectall_arrayref(q/select version from v$instance/);
print "DB Version: ", $v->[0][0],"\n";
do_it($dbh, 0, 0);
do_it($dbh, 1, 1);
sub do_it {
my ($dbh, $txn, $raise) = @_;
print "AutoCommit = ", !$txn, " RaiseError = $raise", "\n";
$dbh->begin_work if $txn;
$captured_error = undef;
$captured_warning = undef;
eval {$dbh->do(q/drop table mytest/);};
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
if ($raise) {
$dbh->{RaiseError} = 1;
} else {
$dbh->{RaiseError} = 0;
}
my $sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->bind_param(1, 1);
$sth->bind_param(2, 'onetwothree');
$sth->execute;
$sth->bind_param_array(1, [51,1,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
my (@tuple_status, $sts, $total_affected);
$sts = 99;
$total_affected = 99;
eval {
($sts, $total_affected) = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
1;
};
print "execute_array " . ($@ ? '' : 'did not') . " raise error: " .
DBI::neat($@) . "\n";
print "execute_array = ", DBI::neat($sts), "\n";
print "total affected rows = ", DBI::neat($total_affected), "\n";
print "Error from execute_array - errstr=", $sth->errstr, ", err=",
$sth->err, ", state=", DBI::neat($sth->state), "\n";
print Data::Dumper->Dump([\@tuple_status], [qw(tuple_status)]), "\n";
print "Error captured in handler: ",
DBI::neat($captured_error), "\n";
print "Warning captured in SIGWARN handler: ",
DBI::neat($captured_warning), "\n";
$dbh->commit if $txn;
my $res = $dbh->selectall_arrayref(q/select * from mytest/);
print Data::Dumper->Dump([$res], ['select * from mytest']), "\n";
}
use strict;
use Data::Dumper;
use warnings;
my ($captured_warning, $captured_error);
$SIG{__WARN__} = sub { $captured_warning = $_[0]; warn $_[0]};
sub error_handler
{
print "Error Handler called\n";
print Data::Dumper->Dump([\@_], [qw(captured_error_in_handler)]);
my ($msg, $handle, $val) = @_;
$captured_error = "$msg";
0; # pass errors on
}
my $dbh = DBI->connect(
'DBI:Oracle:host=betoracle.easysoft.local;sid=devel', 'bet', 'b3t',
{ RaiseError => 1, PrintError => 0,
#PrintWarn => 1,
#ora_chunk_array_size => 2,
HandleError => \&error_handler
});
my $v = $dbh->selectall_arrayref(q/select version from v$instance/);
print "DB Version: ", $v->[0][0],"\n";
do_it($dbh, 0, 0);
do_it($dbh, 1, 1);
sub do_it {
my ($dbh, $txn, $raise) = @_;
print "AutoCommit = ", !$txn, " RaiseError = $raise", "\n";
$dbh->begin_work if $txn;
$captured_error = undef;
$captured_warning = undef;
eval {$dbh->do(q/drop table mytest/);};
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
if ($raise) {
$dbh->{RaiseError} = 1;
} else {
$dbh->{RaiseError} = 0;
}
my $sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->bind_param(1, 1);
$sth->bind_param(2, 'onetwothree');
$sth->execute;
$sth->bind_param_array(1, [51,1,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
my (@tuple_status, $sts, $total_affected);
$sts = 99;
$total_affected = 99;
eval {
($sts, $total_affected) = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
1;
};
print "execute_array " . ($@ ? '' : 'did not') . " raise error: " .
DBI::neat($@) . "\n";
print "execute_array = ", DBI::neat($sts), "\n";
print "total affected rows = ", DBI::neat($total_affected), "\n";
print "Error from execute_array - errstr=", $sth->errstr, ", err=",
$sth->err, ", state=", DBI::neat($sth->state), "\n";
print Data::Dumper->Dump([\@tuple_status], [qw(tuple_status)]), "\n";
print "Error captured in handler: ",
DBI::neat($captured_error), "\n";
print "Warning captured in SIGWARN handler: ",
DBI::neat($captured_warning), "\n";
$dbh->commit if $txn;
my $res = $dbh->selectall_arrayref(q/select * from mytest/);
print Data::Dumper->Dump([$res], ['select * from mytest']), "\n";
}
DB Version: 11.1.0.6.0
AutoCommit = 1 RaiseError = 0
# the following output is from PrintWarn:
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: '' # which we'd expect as RaiseError is off
execute_array = undef # good as at least execute_array returned an error status
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [ # indicates an error for those that look at this
-1,
[
1,
'ORA-00001: unique constraint (BET.SYS_C00102758) violated (
DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
# warning captured in SIGWARN handler:
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # oh dear, no rows inserted from the batch
[
'1',
'onetwothree '
]
];
AutoCommit = RaiseError = 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: '' # oh dear, RaiseError on and no error raised
execute_array = undef
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (BET.SYS_C00102759) violated (
DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef # error handler never called!
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # no rows from batch inserted!
[
'1',
'onetwothree '
]
];
AutoCommit = 1 RaiseError = 0
# the following output is from PrintWarn:
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: '' # which we'd expect as RaiseError is off
execute_array = undef # good as at least execute_array returned an error status
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [ # indicates an error for those that look at this
-1,
[
1,
'ORA-00001: unique constraint (BET.SYS_C00102758) violated (
DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
# warning captured in SIGWARN handler:
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # oh dear, no rows inserted from the batch
[
'1',
'onetwothree '
]
];
AutoCommit = RaiseError = 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: '' # oh dear, RaiseError on and no error raised
execute_array = undef
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (BET.SYS_C00102759) violated (
DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef # error handler never called!
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # no rows from batch inserted!
[
'1',
'onetwothree '
]
];
DB Version: 10.2.0.1.0
AutoCommit = 1 RaiseError = 0
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: ''
execute_array = undef # error not raised, expected as RaiseError not set
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (SYSTEM.SYS_C005657) violated
(DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # the good rows in the batch are commited, good
[
'1',
'onetwothree '
],
[
'51',
'fiftyone '
],
[
'52',
'fiftythree '
],
[
'53',
'one '
]
];
AutoCommit = RaiseError = 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: '' # not error raised even with RaiseError set!
execute_array = undef
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (SYSTEM.SYS_C005658) violated
(DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # good rows committed, good
[
'1',
'onetwothree '
],
[
'51',
'fiftyone '
],
[
'52',
'fiftythree '
],
[
'53',
'one '
]
];
AutoCommit = 1 RaiseError = 0
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: ''
execute_array = undef # error not raised, expected as RaiseError not set
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (SYSTEM.SYS_C005657) violated
(DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # the good rows in the batch are commited, good
[
'1',
'onetwothree '
],
[
'51',
'fiftyone '
],
[
'52',
'fiftythree '
],
[
'53',
'one '
]
];
AutoCommit = RaiseError = 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in array DML (DBD SUC
CESS_WITH_INFO: OCIStmtExecute) [for Statement "insert into mytest values (?,?)"
] at /home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array_oracle.
pl line 62.
execute_array did not raise error: '' # not error raised even with RaiseError set!
execute_array = undef
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD SUCCESS_
WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (SYSTEM.SYS_C005658) violated
(DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: 'DBD::Oracle::st execute_array warning: ORA
-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for State
ment "insert into mytest values (?,?)"] at /home/martin/svn/dbd-odbc/trunk/rt_da
ta/execute_array/execute_array_oracle.pl line 62.
'
$select * from mytest = [ # good rows committed, good
[
'1',
'onetwothree '
],
[
'51',
'fiftyone '
],
[
'52',
'fiftythree '
],
[
'53',
'one '
]
];
- If you are using execute_array and need to know if some of the batch failed and you are using DBD::Oracle 1.27 or earlier you need to do:
- If you are unsure if your Oracle database is broken run the above script and if it only outputs 1 row you've got a problem. If you locate the patch please let me know.
Trackback URL for this post:
http://www.martin-evans.me.uk/trackback/89
- martinjevans's blog
- Login to post comments
© Martin J. Evans 2007-2013
Powered by Apache/Mysql/Drupal
Syndicate
User login
My professional profileRecent comments
- Quite a bit quieter now
35 weeks 3 days ago - native endianess
37 weeks 6 days ago - Question on alexa search engines
39 weeks 3 days ago - Testing
39 weeks 4 days ago - Win7 64 perl32 ODBC DBI
47 weeks 5 days ago - raspberryperl.org
48 weeks 6 days ago - It's so different today
50 weeks 2 days ago - It worked
1 year 6 days ago - Write access to ext3 from Windows
1 year 5 weeks ago - TAF issues sorted out now
1 year 7 weeks ago
Comments
Fixes in DBD::Oracle 1.28 RC1/RC2