On Sep 4, 11:35 am, [EMAIL PROTECTED] (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.
