| Subject: | Problem with running execute_array |
| Date: | Thu, 13 Apr 2017 07:43:29 +0000 |
| To: | "'bug-DBI [...] rt.cpan.org'" <bug-DBI [...] rt.cpan.org> |
| From: | "Domscheit, Wernfried" <wernfried.domscheit [...] sunrise.net> |
Hi
Some time ago I tried already to submit my issue at https://github.com/perl5-dbi/dbi/issues/35 but apparently nobody checks it there. Perhaps I get a response by this way.
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:\Temp\execute_array.pl line 16.
It works without the APPEND_VALUES hint, but 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:\Temp\execute_array.pl line 16.
Also an empty hash does not work:
$sth->execute_array( {} );
Looks like OCIStmtExecute() (see http://docs.oracle.com/database/121/LNOCI/oci17msc001.htm#LNOCI17163) is executed always in OCI_BATCH_ERRORS mode.
Do you see any possibility to run the statement without BATCH ERROR mode?
perl Version:
This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
uname -a
Linux p-mipmed-as-01.swi.srse.net 3.10.0-514.6.1.el7.x86_64 #1 SMP Sat Dec 10 11:15:38 EST 2016 x86_64 x86_64 x86_64 GNU/Linux
Oracle Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
DBI.pm Version: 1.627
DBD.pm Version: 1.23.00
DBD/Oracle.pm Version: 1.62
Best Regards
Wernfried
Show quoted text
____________________________________
Wernfried Domscheit
Senior Engineer OSS
Infrastructure
Sunrise Communications AG
Thurgauerstrasse 60
8050 Zürich
Phone: +41 58 777 77 77
Mobile: +41 76 777 83 32
wernfried.domscheit@sunrise.net<mailto:wernfried.domscheit@sunrise.net>
www.sunrise.ch<http://www.sunrise.ch/>
Message body is not shown because it is too large.