Problem with fetching LONGs and LONG RAWs (ORA-01062)

From: Krivenok Dmitry <krivenok.dmitry_at_gmail.com>
Date: Mon, 03 Sep 2007 06:39:30 -0700
Message-ID: <1188826770.984093.107950@22g2000hsm.googlegroups.com>

Hello!

I've developed a perl script that works with Oracle via DBI and DBD::Oracle.

This perl script illustrates a strange problem while fetching LONGs and LONG RAWs.

Please look at this code:

###################################################

...
...
my $dbh;
my $ret_val = oracle_connect($dbh, "ORANGE", "krivenok", "krivenok", undef, undef);
die ($ret_val) if defined $ret_val;

my $buffer_size_for_long = 16 * 1024 * 1024 - 48; $dbh->{LongReadLen} = $buffer_size_for_long; my $sth = $dbh->prepare("SELECT longdata FROM lob_example WHERE lob_id = 1");
if(defined $sth)
{

  print "All right!!!\n"
}
else
{

  print "Error : '$DBI::errstr'\n";
}

oracle_disconnect($dbh);

###################################################

This script always fails with error:

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

for buffer size >= 16 * 1024 * 1024 - 48 and always works fine for buffer size < 16 * 1024 * 1024 - 48.

I've executed the script on another server and got the following result:
Works for buffer size < 4 * 1024 * 1024 - 12 Fails for buffer size >= 4 * 1024 * 1024 - 12

So, my questions are:

1) What is define buffer?
2) How can I change its size?
3) What is the maximum size of the buffer?

P.S.
Yes I know that LONG and LONG RAW types are deprecated. We will migrate to using CLOBs and BLOBs in perspective. However, we can't migrate now :( Received on Mon Sep 03 2007 - 08:39:30 CDT