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: h...@wsr.ac.at
> To: dbi-users@perl.org
> 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
> | | | h...@wsr.ac.at | bleiben, mir und andern."
> __/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd
