There seems to be a bug in DBD::Oracle’s execute_array when working with 11g.
If you tell DBD::Oracle to autocommit, it seems that in 11g this commit will not take place when an error occurs during the processing of one of the tuples that you passed into execute_array. So given this table:
CREATE TABLE test_array ( row_1 INTEGER NOT NULL, row_2 INTEGER NOT NULL, row_3 INTEGER NOT NULL)
the following code will result in no records being added to the db.
# create a database handle
my $dbh = DBI->connect('dbi:Oracle:','[email protected]','xx',{
RaiseError => 1,
PrintError => 0,
ShowErrorStatement => 1,
AutoCommit => 1
}) || die "Unable to establish connection with Oracle server: $DBI::errstr";
my $rv;
my @var1 = (1,1,1,1,1,1,1,1,1,1);
my @var2 = (2,2,2,2,'s',2,2,2,2,2);
my @var3 = (3,3,3,3,3,3,3,3,3,3);
my $tuple_status = [];
my $dumped ;
my $rows = [];
$sth->execute_array(
{ArrayTupleStatus => $tuple_status},
\@var1,
\@var2,
\@var2,
);
The workaround is to set AutoCommit => 0 and then use $dbh-->commit() after the statement.
So far, the jury is out whether this is a bug only in 11g, but as this code works correctly in 9 and 10 (it adds the 9 rows), it seems likely. I have noted the bug here: https://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832.
1 Comment. Leave new
Same issue in that 11g will not delete all records, the delete with criteria will drop randon numbers of rows, generally 0 and then use $dbh–>commit(), and seems to be working.