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

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 = 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,87­a6518,0)=ERROR
>
> In accordance withhttp://download-west.oracle.com/docs/cd/B10501_01/appdev.920/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 = 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,87­a6518,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.- 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 --