We have a problem with Oracle Client 19.x 32 bit (we tested the versions 19.3, 19.6. and 19.9) when reading thousands of rows from a table with some CLOB columns.
I made a simple example project to illustrate the problem, which I can send you via E-Mail (or is there meanwhile a possibility to upload test projects ?).
In the example we have a table with five CLOB columns. The values for these columns can be null:
Code: Select all
CREATE TABLE TEST_CLOB_ORACLIENT_19
(
ID VARCHAR2 (32) DEFAULT sys_guid() NOT NULL
, NAME VARCHAR2 (50)
, NOTE1_CLOB CLOB
, NOTE2_CLOB CLOB
, NOTE3_CLOB CLOB
, NOTE4_CLOB CLOB
, NOTE5_CLOB CLOB
)
/
ALTER TABLE TEST_CLOB_ORACLIENT_19
ADD CONSTRAINT PK_TEST_CLOB_ORACLIENT_19 PRIMARY KEY ( ID )
/
DECLARE
vLfdNr integer;
begin
FOR vLfdNr IN 1..80000
LOOP
insert into TEST_CLOB_ORACLIENT_19 ( NAME ) values ( 'Row # ' || vLfdNr );
END LOOP;
end;
/
COMMIT
/
OCI-21503: Program Terminated By Fatal Error
OCI-04030: out of process memory when trying to allocate 16336 bytes (koh-kghu sessi,alloc lob locator)
Code: Select all
-- initialize OraSession
oraQuery := TOraQuery.Create( nil );
oraQuery.Session := OraSession;
oraQuery.SQL.Text := 'select * from TEST_CLOB_ORACLIENT_19';
oraQuery.Open;
while not oraQuery.eof do
oraQuery.next();
// program crashes when approx. 61.000 rows are read
OCI-21503: Program Terminated By Fatal Error
OCI-04030: out of process memory when trying to allocate 16336 bytes (koh-kghu sessi,alloc lob locator)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_skgudmp()+103 CALLrel _kgdsdst() 18D20C 1 594BB690 594BBAF0
594BB690 594BBAF0
_kgefecl()+912 CALLreg 00000000 14CDF80 3
.B201.253()+788 CALLrel _kgefecl() 14CDF80 14D7BF8 0 1 4703210
4703320
_kghalf()+3118 CALLrel _kghnospc() 14CDF80 14E9DB0 3FD0 0
14E9DDC 0
_kghualloc()+793 CALLrel _kghalf() 14CDF80 14E9DB0 3FC4 0 0
596CB8A0
_kohalmc()+352 CALLrel _kghualloc() 14CDF80 14E9DA0 FA4 1
596CB8A0 14E2B20
_kohalc()+1552 CALLrel _kohalmc() 14CDF80 FA0 A 1 596CB8A0 0 0
_kohalw()+85 CALLrel _kohalc() 14CDF80 FA0 A 1 596CB8A0 0 0
.B25.470()+4695 CALLrel _kollalfn() 14CD870 FA0 A 596CB8A0 5 40
_kpugdesc()+26 CALLrel _kpugdesc2() 14CD870 18FA5C 32 1 0 0 0 0
_OCIDescriptorAlloc CALLptr 00000000 14CD870 18FA5C 32 0 0
()+43
007742D0 CALLreg 00000000 14CD870 18FA5C 32 0 0
0076C76A CALLreg 00000000 770A39 770A41 1 33640E0
744578 744578
00770B2B CALL??? 00000000 798A08 0 0 18FB30 0 A0C138
00771298 CALL??? 00000000 18FC44 7719DB 18FC2C 1
33640E0 798A08
0076F4D4 CALLrel 00770EBC 353650 3353650 18FC70 691BCF
18FC50 691BDC
00691BCF CALL??? 00000000 18FC50 691BDC 18FC70 18FCD0
691C20 18FC70
006943BE CALL??? 00000000 59A0C11C 1798A01 2F3CE30
3353650 18FCA0 5B5A99
005B5A99 CALL??? 00000000 798A08 140A251 2F3CE30
33640E0 18FCC8 56D9DE
0056D9DE CALL??? 00000000 1 1 1 6566AF 65657D 1
0056E169 CALL??? 00000000 18FD04 56E245 18FCF8 0 0
2E7F9E0
0056E277 CALL??? 00000000 7B039B 18FD38 7B041E 18FD30 0
2F2FFAC
004E9077 CALLreg 00000000 18FD44 4E90BD 18FD68 18FD70
4E90DA 18FD68
00510F90 CALLreg 00000000 18FD7C 510FA9 18FDB8 18FDCC
51107B 18FDB8
005112E4 CALLrel 00510EE0 18FE30 2EFDE0C 2E7FA48 18FDD8
5112F6 18FE48
00510958 CALLrel 00511158 2EED030 2EECF24 18FE64 5109D6
18FEA0 18FEB0
00510985 CALLrel 0051082C 2E5E360 E04 18FEBC 5109D6
18FEF8 18FF08
005105B0 CALLrel 0051082C 2F0C260 E04 18FF14 5105D0
18FF40 18FF48
007BC54A CALLreg 00000000 18FF54 7BC5B2 18FF88 18FF78
7BC631 18FF88
74EB3438 CALLptr 00000000 7EFDE000 18FFD4 77649812
7EFDE000 73C916E5 0
77649810 CALLreg 00000000 7EFDE000 73C916E5 0 0
7EFDE000 0
776497E0 CALLrel 776497EB 7BC464 7EFDE000 0 0 0 0
00000000 CALL??? 00000000
Call stack signature: 0xa16a5133c6aee296
call stack performance statistics:
total : 0.359000 sec
setup : 0.250000 sec
stack unwind : 0.000000 sec
symbol translation : 0.109000 sec
printing the call stack: 0.000000 sec
printing frame data : 0.000000 sec
printing argument data : 0.000000 sec
printing kernel stack : 0.000000 sec
----- End of Call Stack Trace -----
Obviously there is a resource problem here, it seems that more and more memory is allocated by the oci driver and never released.
The programm runs fine with Oracle Client 12.1.0.2.0.
I played around with some parameters, e.g. PrefetchLobSize, but I could not find a parameter to solve the problem.
I guess the problem is the same described in "GUI program exits silently (low RAM, LOB)" (viewtopic.php?f=5&t=40657).
My Environment:
Oracle Server: 19.0.0.0.0
Oracle Client: 19.9.0.0.0
ODAC: 11.4.3 (latest)
Delphi: XE2
OS: Windows 10 64 bit, Windows 7 64 bit
Thanx for looking into this.
Stefan "ostijo" Grosskreutz