LongReadLen: unable to allocate memory for define buffer

[Jim Ursetto]

Hello,

I am at my wits' end trying to debug a problem with LongReadLen. My
environment is HP-UX 11.11 using a 32-bit Perl. My database is
Oracle 8.1.7.4.0. The code looks like:

$dbh = DBI->connect(@connect);
$dbh->{LongReadLen} = 33_554_432; # 2^25 -- too high
$dbh->prepare($sql); # a SELECT statement

This works fine for LongReadLen <= 33,554,338 bytes (2^25 - 94).
However, once LongReadLen exceeds this number, as it does above,
I receive the error

ORA-01062: unable to allocate memory for define buffer
(DBD ERROR: OCIDefineByPos)

I tried multiple versions of Perl, the Oracle client and modules:

| Perl | DBI | DBD::Oracle | Oracle | Status |
|-------+------+-------------+--------+--------|
| 5.5.3 | 1.12 | 1.02 | 7 | OK |
| 5.5.3 | 1.30 | 1.14 | 8.1.7 | FAIL |
| 5.6.1 | 1.48 | 1.16 | 9.2.0 | FAIL |
| 5.8.8 | 1.53 | 1.19 | 10.2.0 | FAIL |

As you can see only the ancient version of Perl and Oracle works.
In this version I was able to raise LongReadLen to at least 128MB
and I'm sure much higher. However, I am no longer able to compile
such an ancient version on this machine, nor may I use the existing
DBD::Oracle.

Rewriting the code is not an option; I am upgrading the environment (which
triggered the issue) and I cannot change the code. The most I could do is
add a small tweak. However, I did reproduce this issue in a small test
program and can modify that at will.

Why is there a 32MB limit on LongReadLen on this platform?
Any assistance is greatly appreciated--I will have to back out
unless I can solve this issue.

Jim

--
j...@3e8.org / 0x43340710 / 517B C658 D2CB 260D 3E1F 5ED1 6DB3 FBB9 4334 0710

[Jim Ursetto]

I checked the doc below and only ora_array_chunk_size looks at all
relevant. I'll try lowering this parameter tomorrow. However: this
problem occurs as soon as a statement is prepared. No data is read to my
knowledge. Therefore, the execute_for_fetch function has not occurred and
this parameter should never be referenced.

At 07:15pm on 2007 April 17, Steven Lembark did write:

> You might want to check the DBI::Oracle doc's (e.g.,
> <http://search.cpan.org/~pythian/DBD-Oracle-1.19/Oracle.pm>).
>
> Eyeball this for "buffer" and there are few more things
> you might want to tweak (e.g., chunk size) along with
> a bit of advice. I've also found a few things in Google
> on the subject -- most of which relate back to Oracle
> tuning, which may prove to be a problem for you.

[Jim Ursetto]

Thanks for the reply. It may be the case that this is an Oracle
issue, but after significant research I can find no other reports
of this particular issue nor any solutions. The official definition
of the error is "Exceeded the maximum buffer size for current
platform" which is unhelpful. The official solution is "upgrade to
Oracle 8 or later", which is exactly the opposite of my experience.
Unfortunately I'm not a DBA so there is a degree of opacity here.

If anyone knows of memory allocation and buffering tweaks for Oracle
that will solve this problem, please let me know.

At 06:20pm on 2007 April 17, Steven Lembark did write:

> [I wrote]

> > | Perl | DBI | DBD::Oracle | Oracle | Status |
> > |-------+------+-------------+--------+--------|
> > | 5.5.3 | 1.12 | 1.02 | 7 | OK |
> > | 5.5.3 | 1.30 | 1.14 | 8.1.7 | FAIL |
> > | 5.6.1 | 1.48 | 1.16 | 9.2.0 | FAIL |
> > | 5.8.8 | 1.53 | 1.19 | 10.2.0 | FAIL |
>

> > Why is there a 32MB limit on LongReadLen on this platform?

> Looks like an oracle issue: the one that worked
> was O7; the ones that fail are 8+. There are
> various oracle tweaks for memory allocation and
> buffering, you might be hitting one of them.

> I'd start on that end and work back towards DBI
> since Perl's allocation of memory is pretty much
> only limited by the O/S.