Hi,
Today I installed DBD::Oracle 1.21 and DBI 1.605 to run with oracle instant
client 10.2.0.3 on 32-bit linux. (Scientific Linux 4 - basically RHEL4).
I wanted to try out bind_param_inout_array() in order to optimize writing.
Unfortunately, my test program resulted in an "Out of Memory" error. Below
is my program, which used the code in the DBD::Oracle perldoc as
inspiration. For @in_values of 10 items it works, but for 100 it fails with
"Out of Memory". I ran with DBI_TRACE=15=dbitrace.out and found the
following interesting lines:
OCIBindByName(9a79ee4,9a7c974,9a028f8,":p2",placeh_len=3,value_p=9975460,value_sz=160572495,dty=1,indp=9a7c98c,a
lenp=0,rcodep=9a7c984,maxarr_len=0,curelep=0 (*=0),mode=2)=SUCCESS
OCIBindDynamic(9a78fa8,9a028f8,9a7c950,3f6db0,9a7c950,3f6ff0)=SUCCESS
OCIAttrGet(9a78fa8,OCI_HTYPE_BIND,9a7c960,0,31,9a028f8)=SUCCESS
dbd_rebind_ph(): bind :p2 <== ARRAY(0x99c39b4) (inout, not-utf8, csid
39->0->39, ftype 1, csform 0->0, maxlen 160572495,
maxdata_size 0)
OCIAttrSet(9a78fa8,OCI_HTYPE_BIND,bffe65da,0,31,9a028f8)=SUCCESS
<- bind_param_inout_array= 1 at TestInOut.pl line 43 via at
TestInOut.pl line 33
>> execute_array DISPATCH (DBI::st=HASH(0x9a7c110) rc1/1 @2 g0 ima5041
pid#27874) at TestInOut.pl line 44 via at Te
stInOut.pl line 33
-> execute_array in DBD::_::st for DBD::Oracle::st
(DBI::st=HASH(0x9a7c110)~0x9979d8c HASH(0x9979d68)) thr#979e008
>> FETCH DISPATCH (DBI::st=HASH(0x9979d8c) rc1/2 @2 g0 ima404
pid#27874) at /data/phedex/Testbed/sw/slc4_ia32_
gcc345/external/p5-dbi/1.605/lib/site_perl/5.8.5/i386-linux-thread-multi/DBI.pm
line 1851 via at TestInOut.pl line 44
1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x9979d8c)~INNER
'NUM_OF_PARAMS') thr#979e008
.. FETCH DBI::st=HASH(0x9979d8c) 'NUM_OF_PARAMS' = 2 (cached)
1 <- FETCH= 2 at
/data/phedex/Testbed/sw/slc4_ia32_gcc345/external/p5-dbi/1.605/lib/site_perl/5.8.5/i386-linux-thread-
multi/DBI.pm line 1851 via at TestInOut.pl line 44
>> execute_for_fetch DISPATCH (DBI::st=HASH(0x9979d8c) rc1/2 @3 g0
ima5041 pid#27874) at /data/phedex/Testbed/sw/slc
4_ia32_gcc345/external/p5-dbi/1.605/lib/site_perl/5.8.5/i386-linux-thread-multi/DBI.pm
line 1924 via at TestInOut.pl li
ne 44
1 -> execute_for_fetch for DBD::Oracle::st (DBI::st=HASH(0x9979d8c)~INNER
CODE(0x9a7c20c) ARRAY(0x9979d50)) thr#979e00
8
ora_st_execute_array INSERT count=100 (ARRAY(0x99694f4) ARRAY(0x9a7c3a4)
undef)...
OCIBindByName(9a79ee4,9a7b434,9a028f8,":p1",placeh_len=3,value_p=0,value_sz=3,dty=1,indp=0,alenp=0,rcodep=0,maxa
rr_len=0,curelep=0 (*=0),mode=2)=SUCCESS
OCIBindDynamic(9a790e0,9a028f8,9a7b410,3f6db0,9a7b410,3f6ff0)=SUCCESS
OCIBindByName(9a79ee4,9a7c974,9a028f8,":p2",placeh_len=3,value_p=0,value_sz=160572495,dty=1,indp=0,alenp=0,rcode
p=0,maxarr_len=0,curelep=0 (*=0),mode=2)=SUCCESS
OCIBindDynamic(9a78fa8,9a028f8,9a7c950,3f6db0,9a7c950,3f6ff0)=SUCCESS
...
out ':p2' [0,0]: alen 160572496, piece 0
out ':p2' [1,0]: alen 160572496, piece 0
out ':p2' [2,0]: alen 160572496, piece 0
...
I looked up the meaning of 'alen' in OCI documentation and found that it is
related to the maximum size of the parameters to be bound. Could it be that
DBD::Oracle is allocating 160572496 bytes of memory for each entry in the
array? That would explain the "Out of Memory" error ;-)
If this is a known problem with a workaround, I would be most grateful.
Cheers,
Ricky
=== BEGIN CODE ===
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use DBD::Oracle;
use Data::Dumper;
my $data_source = 'dbi:Oracle:XXX';
my $username = XXX
my $auth = XXX
my %attr = ( RaiseError => 1,
AutoCommit => 0 );
print "Connecting...";
my $dbh = DBI->connect($data_source, $username, $auth, \%attr);
print "Done.\n";
eval {
print "Dropping test objects...";
$dbh->do( qq{ drop table foo } );
$dbh->do( qq{ drop sequence foo_id_seq } );
print "Done\n";
};
warn $@ if $@;
print "Creating test objects...";
$dbh->do( qq{ create table foo (id number, bar number) } );
$dbh->do( qq{ create sequence foo_id_seq } );
print "Done.\n";
eval {
my @in_values=(1..100);
my @out_values;
my @status;
my $sth = $dbh->prepare(qq{
INSERT INTO foo (id, bar)
VALUES (foo_id_seq.nextval, ?)
RETURNING id INTO ?
});
$sth->bind_param_array(1,[EMAIL PROTECTED]);
$sth->bind_param_inout_array(2,[EMAIL PROTECTED], 0); #,{ora_type =>
ORA_VARCHAR2});
$sth->execute_array({ArrayTupleStatus=>[]}) or die "error inserting";
foreach my $id
(@out_values){
print 'returned id='.$id."\n";
}
};
warn $@ if
$@;
print "Dropping test
objects...";
$dbh->do( qq{ drop table foo } );
$dbh->do( qq{ drop sequence foo_id_seq }
);
print "Done\n";
$dbh->disconnect();
exit;
=== END CODE ===
