Beware execute_array with DBI/DBD::Oracle

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:
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";
}
For a broken Oracle database and DBD::Oracle 1.27 you'll get:
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         '
                          ]
                        ];
Even for a working Oracle database we get:
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                 '
                          ]
                        ];
So, my advice:
  1. 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:
    # set RaiseError on your connection or statement handle
    my $ret = eval {
      execute_array();
    };
    die "error from batch" if $@ || !$ret;
  2. 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.
UPDATE: I should have said John Scoles is working to fix the DBD::Oracle issue for the next release - thanks John.

Comments

Fixes in DBD::Oracle 1.28 RC1/RC2

John Scoles has done a sterling job of looking into the incompatibilities with the DBI specification in DBD::Oracle. DBD::Oracle 1.28 RC1 and RC2 include a replacement 26exe_array.t I wrote for DBD::ODBC which now verifies the changes John has made now align with the DBI specification. You can find the latest release candidate of DBD::Oracle at John's blog.