| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with fetching LONGs and LONG RAWs (ORA-01062)
On Sep 4, 12:05 pm, Krivenok Dmitry <krivenok.dmi..._at_gmail.com> wrote:
> On Sep 3, 5:39 pm, 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 made some other tests and determined that the number
> 16 * 1024 * 1024 - 48 is valid only for LONGs.
> For LONG RAWs the limit is 8 * 1024 * 1024 - 25.
>
>
>
> > 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 :(
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
!! 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.
Received on Tue Sep 04 2007 - 16:59:11 CDT
 |
 |