execute_array returns nonzero and no error tuples when INSERT failed

[PetrN1974]

I am using execute_array on an array of 10000 elements. Even if that INSERT fails because of a lack of space in Oracle, execute_array and array tuples all indicate success; only the statement handle $sth->err() method shows the error. That violates the documented use of execute_array (checking the returned status and checking the tuples).

Set up for the code below: Create a table EXPERIMENTAL in a tablespace constrained to 10MB. The table has a single column, COL VARCHAR(2000).

use strict;
use DBD::Oracle;

my $dbh = DBI->connect("dbi:Oracle:", 'connect code', '', {RaiseError => 1,PrintError => 0,AutoCommit => 0,RowCacheSize => 1000}
) or die "Database connection could not be established: $DBI::errstr";

my $sth = $dbh->prepare( "insert into experimental values (?)" );
$sth->{RaiseError} = 0;

my $bigValue = ("x") x 1500;
my @BigArray = ($bigValue) x 10000;

my $ret = $sth->execute_array( {ArrayTupleStatus=>\my @tuple_status},@BigArray );
print "Inserted big array, got '$ret'\n";
if ( not $ ret ) {
foreach my $status (@tuple_status) {
$status = [0, "Skipped"] unless defined $status;
next unless ref $status;
print( "Failed to insert a row: $status->[1]\n" );
}
die "Failed on DB handling, going down";
}

use Data::Dumper; print Dumper @tuple_status;
print "Statement has ",$sth->err()," and ",$sth->errstr(),"\n";

I expect that this code will show a $ret of zero (false, undef, whatever). I expect that at least one of @tuple_status'es if a reference to an error message.

What I actually get is $ret=10000 and @tuple_status being all -1's; according to DBI docs and samples, I interpret it as "all successful", and that is not the case. Only the last print gives me
"Statement has 1653 and ORA-01653: unable to extend table ***.EXPERIMENTAL by 128 in tablespace EXPERIMENTAL (DBD ERROR: OCIStmtExecute)"

DBD::Oracle version is 1.74. Oracle version is 12 (both server and client).

[djzort]

1.74 is out of date, please see if you can replicate with 1.76

[PetrN1974]

Replicated in 1.76. The exact same behaviour. $sth->execute_array() returns 10000 (i.e. success). @tuple_status contains only minus-ones, i.e. not references (i.e. success). Only $sth->err() tells me there was an ORA-01653 error (unable to extend table) that the code is expected to report.

…

______________________________________________________________

Od: "Dean Hamstead" ***@***.***> Komu: "perl5-dbi/DBD-Oracle" ***@***.***> Datum: 26.03.2019 08:17 Předmět: Re: [perl5-dbi/DBD-Oracle] execute_array returns nonzero and no error tuples when INSERT failed (#80) 1.74 is out of date, please see if you can replicate with 1.76 -- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #80 (comment)

[mjegh]

My recollection is that Oracle does not provide a per row status and the -1 states the status is unknown. I'd have to check the code to confirm but I'm fairly sure that is the case. I don't believe there is a "violation" of the DBI docs here so please point me at the section you quote.

[PetrN1974]

It's a misunderstanding. I am not complaining about "violating the specs by having tuple_array of -1 states". I am complaining about execute_array returning success when it clearly failed. Directly from the horse's mouth: https://metacpan.org/pod/DBI#execute_array my $tuples = $sth->execute_array( { ArrayTupleStatus => \my @tuple_status }, \@first_names, \@last_names, ); if ($tuples) { print "Successfully inserted $tuples records\n"; } In the test shown, $tuples is 10000, even though $sth->execute_array failed. Mind you: I am not complaining that @tuples_array is minus-ones, specifically. That is just a data point for consideration. DBI documentation says that execute_array must return a boolean false value in case the statement failed, and in my test case this specification is violated by DBD::Oracle. If it helps the clarity, just delete the whole part about tuples_array from the bugreport; keep just the part about $tuples being boolean true.

…

______________________________________________________________

Od: "Martin J. Evans" ***@***.***> Komu: "perl5-dbi/DBD-Oracle" ***@***.***> Datum: 29.03.2019 11:05 Předmět: Re: [perl5-dbi/DBD-Oracle] execute_array returns nonzero and no error tuples when INSERT failed (#80) CC: ***@***.***> My recollection is that Oracle does not provide a per row status and the -1 states the status is unknown. I'd have to check the code to confirm but I'm fairly sure that is the case. I don't believe there is a "violation" of the DBI docs here so please point me at the section you quote. -- You are receiving this because you authored the thread. Reply to this email directly or view it on GitHub: #80 (comment)