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

On Sep 4, 2:27 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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?

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production

> What is the version of the DBI?

1.53

> What platform is Perl running on?

Linux develop 2.6.14-gentoo-r2 #1 SMP PREEMPT Wed Mar 21 18:43:52 MSK 2007 i686 Intel(R) Pentium(R) 4 CPU 2.80GHz GenuineIntel GNU/Linux

> And of the DBI requires an Oracle cleint to be installed, what is the
> Oracle cleint version?

10.2.0.3.0

>
> HTH -- Mark D Powell --
Received on Tue Sep 04 2007 - 02:32:12 CDT