[DBD::Oracle] ORA-02005 on array insert

[Peter J. Holzer]

A few months ago we upgraded two of our servers from RHEL 4 to RHEL6.
Unfortunately the upgrade involved the OS, the Perl version (5.8.8 ->
5.10.1), the Oracle client version (still 10g, but now instant client)
and DBD::Oracle (1.19 -> 1.30 (now 1.38)), and we didn't note the
problem at once, so it's unclear which of the many changes is the
culprit, but I suspect it's a bug in DBD::Oracle.

Anyway, since the upgrade sometimes (not always) array inserts returned
the error 

    DBD::Oracle::st execute_array failed: ORA-02005: implicit (-1)
    length not valid for this bind or define datatype (DBD ERROR:
    OCIBindByName) 

Here is a test script which semi-reliably (>= 50% of the time) produces
the error message:


#!/usr/bin/perl

use warnings;
use strict;

use DBI;

my $dbi_credential_file = $ARGV[0];
my ($data_source, $username, $auth) = read_cred($dbi_credential_file);

my $conn_attr = {
   AutoCommit => 0,
   PrintError => 0,
   RaiseError => 1
};
$ENV{NLS_LANG} = '.AL32UTF8';       # real UTF-8
my $dbh = DBI->connect($data_source, $username, $auth, $conn_attr);
$dbh->{FetchHashKeyName} = 'NAME_lc';

$dbh->do("create table bug_ora_02005_$$ (
                set_id       number         not null,
                data_id      number         not null,
                time         number,
                period_start number,
                period_end   number,
                real         number,
                string       number,
                coordtype    number,

                constraint bug_ora_02005_${$}_pk primary key(data_id, set_id)
            )
         ");

my $n = 900;
my $ct_none = undef;

for my $run (1 .. 1_000) {
    print STDERR "run: $run\n";
    my $data_ids;
    my @set_ids;
    for (1 .. $n) {
        push @$data_ids, $_;
        push @set_ids, $run;
    }
    my $sth = $dbh->prepare_cached("insert into bug_ora_02005_$$(period_start, 
period_end, data_id, set_id, coordtype)
                                                          values(?,            
?,          ?,       ?,      ?        )");

    $sth->execute_array({}, undef, undef, $data_ids, \@set_ids, $ct_none);
}
$dbh->commit();

$dbh->do("drop table bug_ora_02005_$$");
$dbh->disconnect();

sub read_cred {
    my ($fn) = @_;

    open(my $fh, "<$fn") or die "cannot open $fn: $!";
    my $line = <$fh>;
    my @cred = split(/[\s\n]+/, $line);
    return @cred;
}
__END__

Some notes:

ARGV[0] is supposed to be the name of a file containing space-separated
connect data, e.g.:
dbi:Oracle:ORCL scott tiger

When the script fails, it always fails on the second iteration of the
loop. If it gets through the second iteration it completes all 1000
iterations successfully.

When the call to prepare_cached is replaced by a simple prepare, the
script fails less often. 

I have run the script with DBI_TRACE=4, but I don't see any significant
difference between successful and unsuccessful runs.

        hp

-- 
   _  | Peter J. Holzer    | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR       | eingeprägt stehen: "Ein ewig Rätsel will ich
| |   | h...@wsr.ac.at      | bleiben, mir und andern."
__/   | http://www.hjp.at/ |    -- Wolfram Heinrich in desd

signature.asc
Description: Digital signature