This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: $dbh->prepare dies
From: "Shchuka.org Oracle Admin" <oracle@shchuka.org>
Date: Tue, 6 Jun 2000 13:05:25 -0400 (EDT)
On Mon, 5 Jun 2000, Shchuka.org Oracle Admin wrote:
> I'm having problem withe $dbh->prepare() call. The system setup is the
> following:
>
> HP-UX 10.20 with perl 5.005_03, DBI 1.13, DBD::Oracle 1.03
> HP-UX 11.0 with Oracle 8.1.6 database
>
> This is the sequence of step I'm taking (approximately):
>
> $dbh = DBI->connect("dbi:Oracle:SID.machine","username","passwd",
> {AutoCommit => 1});
> $dbh->do($stmt1); #this inserts some rows in a table
>
> $sth = $dbh->prepare($stmt2); #this is select of the rows from that table
> #this (above) prepare dies with no messages, etc.
>
> $sth->execute;
> while(@values = $sth->fetchrow_array) {
> # do some stuff here
> }
> $sth->finish;
> $dbh->disconnect();
>
>
> Again, the $sth = $dbh->prepare($stmt2) dies. When I try to execute the
> same statement in SQL*PLUS (same connction), I get a messages "Buffer
> overflow. Change ARRAYSIZE or icrease MAXDATA" (or something like that).
>
> After I do "SET ARRAYSIZE 10", I can execute the query with no problem.
> I've been trying to do something like this from my perl script, but
> nothing worked. Most of my attempts caused the program to die with no
> explanation. The only thing that didn't break anything was
>
> $sth = $dbh->prepare($stmt2,{ArraySize => 10});
>
> but this didn't fix the problem either...
>
> Now I'm totally lost and don't even know where to start... Any ideas are
> more than welcome.
I've received a couple suggestions regarding my problem, but nothing
worked. Apparently, I found the exact error. I had to do this:
eval {
$dbh->prepare($stmt2);
};
print $@ if($@ ne '');
This is what got printed:
Out of memory during "large" request for 67112960 bytes at
/opt/perl-5.005_03/lib/site_perl/5.005/PA-RISC2.0/DBD/Oracle.pm
Question number one: why is it trying to allocate 65 Meg?
Question number two: what should I do to prevent it?
Any help is greatly appreciated,
===
Subject: Re: $dbh->prepare dies
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Tue, 6 Jun 2000 20:58:20 +0100
Try DBI->trace(...);
What's LongReadLen set to?
===
Subject: Re: $dbh->prepare dies
From: Steve Lloyd <slloyd@datigen.com>
Date: Tue, 06 Jun 2000 14:19:20 -0600
According to your error message you need to allocate more memory to the buffer.
$MAXRECSIZE=67112960; #you may need to increase it even larger
$dbh->{LongReadLen} = $MAXRECSIZE;
===
Subject: Re: $dbh->prepare dies
From: "Shchuka.org Oracle Admin" <oracle@shchuka.org>
Date: Tue, 6 Jun 2000 16:43:59 -0400 (EDT)
LongReadLen is set to 67113000 (which is just a little larger than the
amount it complaints about).
Below is the trace from the failing statement (trace level 9!).
Any other ideas?
Great thanks,
-Aleks.
---------------------------------------------------------------
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x406470f4)~0x4069f0b8 '
select ID ID_plus_exp,
Parent_ID parent_id_plus_exp,
LPAD(' ',2*(level-1))||
Operation||
DECODE(other_tag,null,'','*')||
DECODE(options,null,'',' ('||options||')')||
DECODE(object_name,null,'',' of '''||object_name||'''')||
DECODE(object_type,null,'',' '||object_type||')')||
DECODE(id,0,decode(optimizer,null,'',' optimizer='||optimizer))||
DECODE(cost,null,'',' (cost='||cost||
DECODE(cardina...')
dbih_setup_handle(DBI::st=HASH(0x4069f184)=>DBI::st=HASH(0x405fb7ec), DBD::Oracle::st, 4064710c, Null!)
dbih_make_com(DBI::db=HASH(0x4069f0b8), DBD::Oracle::st, 240)
dbih_setup_attrib(DBI::st=HASH(0x405fb7ec), Err, DBI::db=HASH(0x4069f0b8)) SCALAR(0x40104ddc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x405fb7ec), State, DBI::db=HASH(0x4069f0b8)) SCALAR(0x4014d280) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x405fb7ec), Errstr, DBI::db=HASH(0x4069f0b8)) SCALAR(0x40105894) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x405fb7ec), Handlers, DBI::db=HASH(0x4069f0b8)) ARRAY(0x4017f5d0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x405fb7ec), Debug, DBI::db=HASH(0x4069f0b8)) 9 (already defined)
dbd_st_prepare'd sql f4
dbd_describe (for sql f4 after oci f54, lb 80, csr 0x40680778)...
fbh 1: 'ID_PLUS_EXP' NULLable, otype 2-> 5, dbsize 22/40, p38.s0
out: ftype 5, bufl 41. indp 0, rlen 0, rcode 0
fbh 2: 'PARENT_ID_PLUS_EXP' NULLable, otype 2-> 5, dbsize 22/40, p38.s0
out: ftype 5, bufl 41. indp 0, rlen 0, rcode 0
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x405fb7ec)~INNER)
<- DESTROY= undef at mgr_common.pl line 6463.
---------------------------------------------------------------
===
Subject: Re: $dbh->prepare dies
From: "Michael A. Chase" <mchase@ix.netcom.com>
Date: Tue, 6 Jun 2000 13:55:34 -0700
Try a smaller LongReadLen. I quote from the fine manual (perldoc DBI):
" Note that the value used here has a direct effect on the memory used
" by the application, so don't be too generous. It's also a good idea
" to use values which are just smaller than a power of 2, e.g., 2**16-2
" which is 65534 bytes.
===