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

Ran into that one, or nearly like it, a while back.  Was not able to find a OCI/DBD solution so I just modified my code like this
 
$sth->execute_array({},\ @undefs, \@undefs, \@data_ids, \@set_ids, $ct_none);

 so I had an array for each param
 
a quick kludge only.
 
If I get a little time this week I will try to look into it.
 
Cheers
John
 

> Date: Tue, 20 Mar 2012 13:12:56 +0100
> From: [email protected]
> To: [email protected]
> Subject: [DBD::Oracle] ORA-02005 on array insert
> 
> 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
> | | | [email protected] | bleiben, mir und andern."
> __/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd