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

On Sep 4, 11:35 am, krivenok.dmi...@xxxxxxxxx (Krivenok Dmitry) 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 :(

My system:
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

My database:
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

Oracle client version:
10.2.0.3.0

DBI version:
1.58

DBD::Oracle version:
1.19

Error is returned by OCIDefineByPos function:
OCIDefineByPos(8773720,87bf1e4,876e958,1,a70e9008,209715204,95,87a7e68,0,87a6518,0)=ERROR

In accordance with
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96584/oci15r34.htm
the number 209715204 is the size of buffer in bytes.

I request 100 * 1024 * 1024 buffer size in my script.
Note that 209715204 = 100 * 1024 * 1024 - 4.

I seems to me that Oracle then compares passed buffer size with some
pre-defined limit
and emits the error if passed value is greater than the limit.

The question is how to determine this limit and how to change it?

Trace results are:

DBI::db=HASH(0x873b728) trace level set to 0x7fffff00/15 (DBI @
0x0/0) in DBI 1.53-nothread (pid 4789)
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x873b0c8)~0x873b728
'SELECT longdata FROM lob_example WHERE lob_id = 1 FOR UPDATE NOWAIT')

dbih_setup_handle(DBI::st=HASH(0x873bc74)=>DBI::st=HASH(0x873bb3c),
DBD::Oracle::st, 873bcd4, Null!)
dbih_make_com(DBI::db=HASH(0x873b728), 873fd48, DBD::Oracle::st,
216, 0) thr#0
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), Err,
DBI::db=HASH(0x873b728)) SCALAR(0x82163d0) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), State,
DBI::db=HASH(0x873b728)) SCALAR(0x8216430) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), Errstr,
DBI::db=HASH(0x873b728)) SCALAR(0x8216400) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), TraceLevel,
DBI::db=HASH(0x873b728)) 2147483647 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), FetchHashKeyName,
DBI::db=HASH(0x873b728)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), HandleSetErr,
DBI::db=HASH(0x873b728)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x873bb3c), HandleError,
DBI::db=HASH(0x873b728)) undef (not defined)
OCIHandleAlloc(8759308,87a7968,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(8773720,876e958,'SELECT longdata FROM
lob_example WHERE lob_id = 1 FOR UPDATE NOWAIT',67,1,0)=SUCCESS
OCIAttrGet(8773720,OCI_HTYPE_STMT,87a796c,
0,24,876e958)=SUCCESS
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 104857600)...
OCIStmtExecute(876e8e4,8773720,876e958,0,0,0,0,16)=SUCCESS
OCIAttrGet(8773720,OCI_HTYPE_STMT,bfa4a20c,
0,18,876e958)=SUCCESS
OCIParamGet(8773720,4,876e958,87bf1e0,1)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,
87bf1f8,0,2,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1fa,
0,1,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,
87bf208,0,285,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf20a,
0,286,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf20c,
0,31,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf20e,
0,32,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1fc,
0,5,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1fe,
0,6,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1ff,
0,7,876e958)=SUCCESS
OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,
87bf200,bfa4a208,4,876e958)=SUCCESS
col 1: dbtype 24, scale 0, prec 0, nullok 1, name LONGDATA
: dbsize 65535, char_used 0, char_size 0, csid 0, csform 0,
disize 209715200
fbh 1: 'LONGDATA' NULLable, otype 24-> 95, dbsize
65535/209715200, p0.s0

OCIAttrSet(8773720,OCI_HTYPE_STMT,bfa4a208,4,13,876e958)=SUCCESS

OCIAttrSet(8773720,OCI_HTYPE_STMT,bfa4a204,4,11,876e958)=SUCCESS
row cache OCI_ATTR_PREFETCH_ROWS 1, OCI_ATTR_PREFETCH_MEMORY 0

OCIDefineByPos(8773720,87bf1e4,876e958,1,a70e9008,209715204,95,87a7e68,0,87a6518,0)=ERROR
OCIErrorGet(876e958,1,"<NULL>",bfa4a068,"ORA-01062: unable to
allocate memory for define buffer
",1024,2)=SUCCESS
OCIErrorGet after OCIDefineByPos (er1:ok): -1, 1062: ORA-01062:
unable to allocate memory for define buffer

OCIErrorGet(876e958,2,"<NULL>",bfa4a068,"ORA-01062: unable to
allocate memory for define buffer
",1024,2)=NO_DATA
dbd_describe'd 1 columns (row bytes: 65535 max, 65535 est avg,
cache: 1)
>> DESTROY DISPATCH (DBI::st=HASH(0x873bc74) rc1/1 @1 g0 ima4
pid#4789) at ./oragate-ng line 2627 via at ./oragate-ng line 2627
<> DESTROY(DBI::st=HASH(0x873bc74)) ignored for outer handle
(inner DBI::st=HASH(0x873bb3c) has ref cnt 1)
>> DESTROY DISPATCH (DBI::st=HASH(0x873bb3c) rc1/1 @1 g0 ima4
pid#4789) at ./oragate-ng line 2627 via at ./oragate-ng line 2627
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x873bb3c)~INNER)
dbd_st_destroy
OCIHandleFree(8773720,OCI_HTYPE_STMT)=SUCCESS
ERROR: '1062' 'ORA-01062: unable to allocate memory for define
buffer (DBD ERROR: OCIDefineByPos)' (err#1)
<- DESTROY= undef at ./oragate-ng line 2627 via at ./oragate-ng
line 2627
DESTROY (dbih_clearcom) (sth 0x873bb3c, com 0x87a78e8, imp
DBD::Oracle::st):
FLAGS 0x102111: COMSET Warn PrintError PrintWarn
ShowErrorStatement
PARENT DBI::db=HASH(0x873b728)
KIDS 0 (0 Active)
IMP_DATA undef
LongReadLen 104857600
NUM_OF_FIELDS 1
NUM_OF_PARAMS 0
dbih_clearcom 0x873bb3c (com 0x87a78e8, type 3) done.

!! ERROR: '1062' 'ORA-01062: unable to allocate memory for define
buffer (DBD ERROR: OCIDefineByPos)' (err#0)
<- prepare= undef at ./oragate-ng line 2627 via at ./oragate-ng
line 2374
>> FETCH DISPATCH (DBI::db=HASH(0x873b728) rc2/3 @2 g0
ima404 pid#4789) at ./oragate-ng line 2627 via at ./oragate-ng line
2374
1 -> FETCH for DBD::Oracle::db (DBI::db=HASH(0x873b728)~INNER
'ParamValues')
.. FETCH DBI::db=HASH(0x873b728) 'ParamValues' = undef
ERROR: '1062' 'ORA-01062: unable to allocate memory for define
buffer (DBD ERROR: OCIDefineByPos)' (err#0)
1 <- FETCH= undef at ./oragate-ng line 2627 via at ./oragate-ng
line 2374
DBD::Oracle::db prepare failed: ORA-01062: unable to allocate memory
for define buffer (DBD ERROR: OCIDefineByPos) [for Statement "SELECT
longdata FROM lob_example WHERE lob_id = 1 FOR UPDATE NOWAIT"] at ./
oragate-ng line 2627.

.

Relevant Pages

  • Oracle TO_TIMESTAMP_TZ Remote System Buffer Overrun (#NISR16022003b)
    ... Oracle TO_TIMESTAMP_TZ Remote System Buffer Overrun ... Oracle's database server contains fuctions for use within queries. ... By supplying a long character string for the second parameter an attacker ...
    (NT-Bugtraq)
  • Re: Buffer or Realloc?
    ... better to allocate memory and realloc it for the size of the what is ... between deciding to use a fixed size buffer or allocating memory ... becomes extremely small when I realloc it. ... I would have to allocate memory. ...
    (comp.lang.c)
  • Re: paging help is needed
    ... -because the virtual memory page containing your data buffer will be ... The trend is after heavy paging activity the system starts ... The main application is oracle database server(this is a datawarehouse ... data) and oracle paralallel query is mostly used. ...
    (linux.redhat)
  • Re: Dynamic string allocation
    ... >> Is there a way to allocate memory for a character array without ... > to preallocate a buffer and fill it with incoming data. ... The simplest method is to get and compile ggets, ...
    (comp.lang.c)
  • Re: Problem with fetching LONGs and LONG RAWs (ORA-01062)
    ... undef, undef); ... What is the maximum size of the buffer? ... unable to allocate memory for define buffer ... buffer (DBD ERROR: OCIDefineByPos)' ...
    (comp.databases.oracle.misc)