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

On Sep 3, 9:39 am, Krivenok Dmitry <krivenok.dmi..._at_gmail.com> wrote:
> 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 :(

Krivenok, I cannot answer your quest but I know a few pieces of information that someone knowlegable in Perl would probably like to know in order to answer your question:

What is the full version of Oracle?
What is the version of the DBI?
What platform is Perl running on?
And of the DBI requires an Oracle cleint to be installed, what is the Oracle cleint version?

HTH -- Mark D Powell -- Received on Mon Sep 03 2007 - 17:27:53 CDT