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:','xxx@xxx','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: http://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832.

You can follow any responses to this post through the RSS 2.0 feed. You can leave a response, or trackback from your own site.