Method execute_array: mandatory ArrayTupleStatus?

[Wernfried]

I have to insert many records into Oracle database and performance is quite crucial at this application.
My Perl script is like this:

use strict;
use DBI;
use DBD::Oracle qw(:ora_types);

my @tuple_status;
my $ora = DBI->connect("dbi:Oracle:<database>", "<user>", "<password>", { PrintError => 1, ShowErrorStatement => 1 } );
$ora->{AutoCommit} = 0;

my $sql = "INSERT /*+ APPEND_VALUES */ INTO T_EXECUTE_ARRAY (PORT) VALUES (?)";
my $sth = $ora->prepare($sql);

my @ports = (1,2,3,4);
$sth->bind_param_array(1, \@{ports} );
$sth->execute_array( { ArrayTupleStatus => \@tuple_status } ) ;

$ora->commit;
$ora->disconnect;

However, I get an error:

DBD::Oracle::st execute_array failed: ORA-38910: BATCH ERROR mode is not supported for this operation (DBD ERROR: OCIStmtExecute) [for Statement "INSERT /*+ APPEND_VALUES */ INTO T_EXECUTE_ARRAY (PORT) VALUES (?)"] at C:\Developing\Source\IMP\Mediation-Mobile\execute_array.pl line 16.

It works without the APPEND_VALUES hint, however then I cannot gain the performance benefits of direct-path inserts.

I am not interested in any errors from ArrayTupleStatus, so I tried without:
$sth->execute_array() ;

But then the error is:
DBI execute_array: invalid number of arguments: got handle + 0, expected handle + between 1 and -1 Usage: $h->execute_array(\%attribs [, @args]) at C:\Developing\Source\IMP\Mediation-Mobile\execute_array.pl line 16.

By some investigations I found a statement like "ArrayTupleStatus becomes optional in DBI version 1.38". Apparently it is still (or again) mandatory.

Version of DPI: 1.627
Version of DBD::Oracle: 1.62
Oracle Version: 12.1.0.2.0

Any idea how to solve this issue? Or should I call Oracle support because of the ORA-38910 error?

[mjegh]

I would suggest posting this issue to the dbi-users mailing list as I believe the "invalid number of arguments" error comes from DBI and not DBD::Oracle.
However, that is not to say DBD::Oracle won't still want an ArrayTupleStatus but one thing at a time.

[Wernfried]

Hi

I like to continue further investigations.

I can call the method with an empty hash ref:
$sth->execute_array( {} ) ;

But I still get the same error:
DBD::Oracle::st execute_array failed: ORA-38910: BATCH ERROR mode is not supported for this operation (DBD ERROR: OCIStmtExecute)

Looks like OCIStmtExecute() is executed always in OCI_BATCH_ERRORS mode.
Do you see any possibility to run the statement without BATCH ERROR mode?

Best Regards
Wernfried

[Wernfried]

I had a closer look at source code and documentation: Currently DBD::Oracle supports only OCI_STMT_SCROLLABLE_READONLY for ora_exe_mode. Do yoe see any possibilty to support also other modes (OCI_BATCH_ERRORS, OCI_COMMIT_ON_SUCCESS, OCI_DEFAULT, etc.) in future release of DBD::Oracle?