Method execute_array: mandatory ArrayTupleStatus?

[Wernfried]

I put this issued already on DBD::Oracle page, however they suggested to post it also here:

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?

[Wernfried]

No response at all? I am a bit disappointed.

Best Regards
Wernfried

[mjegh]

@Wernfried execute_array requires an arg but you can just pass {}. The error you are getting is an Oracle error not an issue with DBD::Oracle. Oracle can't do batches when you add that hint. See https://oracle-base.com/articles/11g/append-values-hint-11gr2

[Wernfried]

No, Oracle cannot return EXCEPTIONS when you use APPEND_VALUES hint. As stated I don't need to retrieve any exception from that statement. How to execute execute_array() without retrieving errors? Looks like OCIStmtExecute() is executed always in OCI_BATCH_ERRORS mode.

[mjegh]

@Wernfried Given the information you supplied my answer was as good as it was going to get. Starting a reply with "No" like that doesn't really encourage me to continue this - it isn't a competition as to who is right. It might be relatively easy to change DBD::Oracle to not use OCI_BATCH_ERRORS but after doing 2 pre-releases and patches which have not been accepted I'm not going to waste my time any further. I'd suggest that if you want this changed you'll probably have to do it yourself.

If you look at https://st.aticpan.org/source/PYTHIAN/DBD-Oracle-1.74/dbdimp.c and search for ora_st_execute_array then look down you'll see it checks (/* Check the `tuples_status' parameter. */). Below there is a oci_mode = OCI_BATCH_ERRORS which you could make dependent on whether if(SvTRUE(tuples_status)) else set oci_mode to 0. No guarantee it will do what you want but I think it will.

[timbunce]

Thanks for the background research @mjegh. Since ArrayTupleStatus already isn't mandatory I don't think there's anything else that would need to change in the DBI. So I'll close this in favour of the DBD::Oracle issue.