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

Mark D Powell (Mark.Powell_at_eds.com) wrote:
: On Sep 4, 5:59 pm, Krivenok Dmitry <krivenok.dmi..._at_gmail.com> wrote:
: > 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 =3D oracle_connect($dbh, "ORANGE", "krivenok", "krivenok",
: > > > undef, undef);
: > > > die ($ret_val) if defined $ret_val;
: >
: > > > my $buffer_size_for_long =3D 16 * 1024 * 1024 - 48;
: > > > $dbh->{LongReadLen} =3D $buffer_size_for_long;
: > > > my $sth =3D $dbh->prepare("SELECT longdata FROM lob_example WHERE lob=
: _id
: > > > =3D 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 >=3D 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 >=3D 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,=
: 87=ADa6518,0)=3DERROR
: >
: > In accordance withhttp://download-west.oracle.com/docs/cd/B10501_01/appde=
: v=2E920/a96584/o...
: > the number 209715204 is the size of buffer in bytes.
: >
: > I request 100 * 1024 * 1024 buffer size in my script.
: > Note that 209715204 =3D 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=3DHASH(0x873b728) trace level set to 0x7fffff00/15 (DBI @
: > 0x0/0) in DBI 1.53-nothread (pid 4789)
: > -> prepare for DBD::Oracle::db (DBI::db=3DHASH(0x873b0c8)~0x873b728
: > 'SELECT longdata FROM lob_example WHERE lob_id =3D 1 FOR UPDATE NOWAIT')
: >
: > dbih_setup_handle(DBI::st=3DHASH(0x873bc74)=3D>DBI::st=3DHASH(0x873bb3c),
: > DBD::Oracle::st, 873bcd4, Null!)
: > dbih_make_com(DBI::db=3DHASH(0x873b728), 873fd48, DBD::Oracle::st,
: > 216, 0) thr#0
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), Err,
: > DBI::db=3DHASH(0x873b728)) SCALAR(0x82163d0) (already defined)
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), State,
: > DBI::db=3DHASH(0x873b728)) SCALAR(0x8216430) (already defined)
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), Errstr,
: > DBI::db=3DHASH(0x873b728)) SCALAR(0x8216400) (already defined)
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), TraceLevel,
: > DBI::db=3DHASH(0x873b728)) 2147483647 (already defined)
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), FetchHashKeyName,
: > DBI::db=3DHASH(0x873b728)) 'NAME' (already defined)
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), HandleSetErr,
: > DBI::db=3DHASH(0x873b728)) undef (not defined)
: > dbih_setup_attrib(DBI::st=3DHASH(0x873bb3c), HandleError,
: > DBI::db=3DHASH(0x873b728)) undef (not defined)
: > OCIHandleAlloc(8759308,87a7968,OCI_HTYPE_STMT,0,0)=3DSUCCESS
: > OCIStmtPrepare(8773720,876e958,'SELECT longdata FROM
: > lob_example WHERE lob_id =3D 1 FOR UPDATE NOWAIT',67,1,0)=3DSUCCESS
: > OCIAttrGet(8773720,OCI_HTYPE_STMT,87a796c,
: > 0,24,876e958)=3DSUCCESS
: > 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)=3DSUCCESS
: > OCIAttrGet(8773720,OCI_HTYPE_STMT,bfa4a20c,
: > 0,18,876e958)=3DSUCCESS
: > OCIParamGet(8773720,4,876e958,87bf1e0,1)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,
: > 87bf1f8,0,2,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1fa,
: > 0,1,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,
: > 87bf208,0,285,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf20a,
: > 0,286,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf20c,
: > 0,31,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf20e,
: > 0,32,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1fc,
: > 0,5,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1fe,
: > 0,6,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,87bf1ff,
: > 0,7,876e958)=3DSUCCESS
: > OCIAttrGet(87b4d0c,OCI_DTYPE_PARAM,
: > 87bf200,bfa4a208,4,876e958)=3DSUCCESS
: > 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)=3DSUCCESS
: >
: > OCIAttrSet(8773720,OCI_HTYPE_STMT,bfa4a204,4,11,876e958)=3DSUCCESS
: > row cache OCI_ATTR_PREFETCH_ROWS 1, OCI_ATTR_PREFETCH_MEMORY 0
: >
: > OCIDefineByPos(8773720,87bf1e4,876e958,1,a70e9008,209715204,95,87a7e68,0,=
: 87=ADa6518,0)=3DERROR
: > OCIErrorGet(876e958,1,"<NULL>",bfa4a068,"ORA-01062: unable to
: > allocate memory for define buffer
: > ",1024,2)=3DSUCCESS
: > 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)=3DNO_DATA
: > dbd_describe'd 1 columns (row bytes: 65535 max, 65535 est avg,
: > cache: 1)
: > >> DESTROY DISPATCH (DBI::st=3DHASH(0x873bc74) rc1/1 @1 g0 ima4
: > pid#4789) at ./oragate-ng line 2627 via at ./oragate-ng line 2627
: > <> DESTROY(DBI::st=3DHASH(0x873bc74)) ignored for outer handle
: > (inner DBI::st=3DHASH(0x873bb3c) has ref cnt 1)
: > >> DESTROY DISPATCH (DBI::st=3DHASH(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=3DHASH(0x873bb3c)~INNER)
: > dbd_st_destroy
: > OCIHandleFree(8773720,OCI_HTYPE_STMT)=3DSUCCESS
: > ERROR: '1062' 'ORA-01062: unable to allocate memory for define
: > buffer (DBD ERROR: OCIDefineByPos)' (err#1)
: > <- DESTROY=3D 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=3DHASH(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=3D undef at ./oragate-ng line 2627 via at ./oragate-ng
: > line 2374
: > >> FETCH DISPATCH (DBI::db=3DHASH(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=3DHASH(0x873b728)~INNER
: > 'ParamValues')
: > .. FETCH DBI::db=3DHASH(0x873b728) 'ParamValues' =3D undef
: > ERROR: '1062' 'ORA-01062: unable to allocate memory for define
: > buffer (DBD ERROR: OCIDefineByPos)' (err#0)
: > 1 <- FETCH=3D 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 =3D 1 FOR UPDATE NOWAIT"] at ./
: > oragate-ng line 2627.- Hide quoted text -
: >
: > - Show quoted text -

: This is late but any chance the memory error is due to the requested
: amount of free memory being unavailable to allocate rather than a
: limit?

: Just being curious cause Perl is on my list of things I would like to
: take up.

: -- Mark D Powell --

--