Zeoslib Portal

This is the official forum of ZeosLib providing support to all ZeosLib users.
https://zeoslib.sourceforge.io/

LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

https://zeoslib.sourceforge.io/viewtopic.php?t=175546

Page 1 of 1

LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 16.12.2022, 12:44
by aehimself
Probably this is going to be a driver issue, as opening the exact same resultset on the exact same machine using the exact same settings, but using OCI 19.8 loads everything perfectly. The new version also loads all data correctly, if LobCacheMode is None.

I just would like to leave it here as it seems either Oracle did it again and shipped a faulty driver, or maybe they changed something which makes Zeos incompatible.
EZSQLException was raised with the message SQL Error: ORA-01013: user requested cancel of current operation

Code: 1013 SQL: OCILobClose

Stack trace:
01638b15 Project1.exe ZDbcOracle 1388 TZOracleConnection.HandleErrorOrWarning
0160bfdc Project1.exe ZDbcOracleResultSet 2854 TZAbstracOracleLobStream.Close
0160c879 Project1.exe ZDbcOracleResultSet 2963 TZAbstracOracleLobStream.Destroy
00dee775 Project1.exe System TObject.Free
01393b5e Project1.exe ZDbcResultSet 4595 TZCodePageConversionStream.Destroy
01610213 Project1.exe ZDbcOracleResultSet 3945 TZOracleRawMultibyteStream32.Create
0160edde Project1.exe ZDbcOracleResultSet 3543 TZOracleClob.CreateLobStream
01394303 Project1.exe ZDbcResultSet 4771 TZAbstractLob.GetStream
0138e0b6 Project1.exe ZDbcResultSet 1696 TZLocalMemCLob.CreateFromClob
0139e1d1 Project1.exe ZDbcCache 1302 SetAsCachedLob
0139e330 Project1.exe ZDbcCache 1309 InternalSetLob
0139e926 Project1.exe ZDbcCache 1370 TZRowAccessor.FillFromFromResultSet
013b7184 Project1.exe ZDbcCachedResultSet 2616 TZCachedResultSet.Fetch
013b7b86 Project1.exe ZDbcCachedResultSet 2882 TZCachedResultSet.MoveAbsolute
01390ae4 Project1.exe ZDbcResultSet 2649 TZAbstractResultSet.Next
016f0df9 Project1.exe ZAbstractRODataset 2130 TZAbstractRODataset.FetchOneRow
016f0d01 Project1.exe ZAbstractRODataset 2106 TZAbstractRODataset.FetchRows
016f203d Project1.exe ZAbstractRODataset 2534 TZAbstractRODataset.GetRecord
0134115e Project1.exe Data.DB TDataSet.GetRecord
01342014 Project1.exe Data.DB TDataSet.GetNextRecord
013425e4 Project1.exe Data.DB TDataSet.GetNextRecords
01341d39 Project1.exe Data.DB TDataSet.SetBufferCount
01341e58 Project1.exe Data.DB TDataSet.UpdateBufferCount
0133e386 Project1.exe Data.DB TDataSet.DoInternalOpen
0133e4a3 Project1.exe Data.DB TDataSet.OpenCursor
0133e25f Project1.exe Data.DB TDataSet.SetActive
0133e000 Project1.exe Data.DB TDataSet.Open
[...]
After this even disconnecting the connection will go in an endless loop, and the usual error pops up when you try to free it:
EZSQLException was raised with the message close all lob streams before closing the resultset

Stack trace:
0138efca Project1.exe ZDbcResultSet 1979 TZAbstractResultSet.Close
013b7598 Project1.exe ZDbcCachedResultSet 2701 TZCachedResultSet.AfterClose
0138f056 Project1.exe ZDbcResultSet 1993 TZAbstractResultSet.Close
016e0470 Project1.exe ZAbstractDataset 441 TZAbstractRWDataSet.InternalUnPrepare
016f6e86 Project1.exe ZAbstractRODataset 4095 TZAbstractRODataset.SetPrepared
016f99b6 Project1.exe ZAbstractRODataset 5266 TZAbstractRODataset.Unprepare
016efbf8 Project1.exe ZAbstractRODataset 1684 TZAbstractRODataset.Destroy
016dfe88 Project1.exe ZAbstractDataset 296 TZAbstractRWDataSet.Destroy
016e2808 Project1.exe ZAbstractDataset 1044 TZAbstractRWTxnSeqDataSet.Destroy
016e2dbf Project1.exe ZAbstractDataset 1135 TZAbstractRWTxnUpdateObjDataSet.Destroy
00dee775 Project1.exe System TObject.Free
00fbc3ca Project1.exe System.Classes TComponent.DestroyComponents
00fbbbba Project1.exe System.Classes TComponent.Destroy
011b4857 Project1.exe Vcl.Controls TControl.Destroy
011bbb76 Project1.exe Vcl.Controls TWinControl.Destroy
0115df5e Project1.exe Vcl.Forms TScrollingWinControl.Destroy
[...]

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 03.03.2023, 10:41
by aehimself
21.9 is also affected.

Code: Select all

EZSQLException was raised with the message SQL Error: ORA-01013: user requested cancel of current operation
 
Code: 1013 SQL: OCILobClose

Stack trace:
00d71152 MyApplication.exe ZDbcOracle     1388 TZOracleConnection.HandleErrorOrWarning
00d4468c MyApplication.exe ZDbcOracleResultSet 2854 TZAbstracOracleLobStream.Close
00d44f29 MyApplication.exe ZDbcOracleResultSet 2963 TZAbstracOracleLobStream.Destroy
0051eaf5 MyApplication.exe System          TObject.Free
00ac9fbd MyApplication.exe ZDbcResultSet    4595 TZCodePageConversionStream.Destroy
00d488c3 MyApplication.exe ZDbcOracleResultSet 3950 TZOracleRawMultibyteStream32.Create
00d4748e MyApplication.exe ZDbcOracleResultSet 3548 TZOracleClob.CreateLobStream
00aca763 MyApplication.exe ZDbcResultSet    4771 TZAbstractLob.GetStream
00ac4526 MyApplication.exe ZDbcResultSet    1696 TZLocalMemCLob.CreateFromClob
00ad45f1 MyApplication.exe ZDbcCache      1302 SetAsCachedLob
00ad4750 MyApplication.exe ZDbcCache      1309 InternalSetLob
00ad4d46 MyApplication.exe ZDbcCache      1370 TZRowAccessor.FillFromFromResultSet
00aed564 MyApplication.exe ZDbcCachedResultSet 2616 TZCachedResultSet.Fetch
00aedf66 MyApplication.exe ZDbcCachedResultSet 2882 TZCachedResultSet.MoveAbsolute
00ac6f54 MyApplication.exe ZDbcResultSet    2649 TZAbstractResultSet.Next
00e29009 MyApplication.exe ZAbstractRODataset 2130 TZAbstractRODataset.FetchOneRow
00e28efe MyApplication.exe ZAbstractRODataset 2102 TZAbstractRODataset.FetchRows
00e2d7f9 MyApplication.exe ZAbstractRODataset 3564 TZAbstractRODataset.FetchAll
0116089a MyApplication.exe uSQLActionThread   780 TOpenThread.InternalExecute
00fc83ba MyApplication.exe uWorkerThread    123 TWorkerThread.Execute
006eb240 MyApplication.exe System.Classes      ThreadProc
00521b1a MyApplication.exe System          ThreadWrapper
00613989 MyApplication.exe madExcept        ThreadExceptFrame
7ff8a572 KERNEL32.DLL              BaseThreadInitThunk
7ff8a586 ntdll.dll                RtlUserThreadStart
Increasing all 3 buffers (INTERNAL_BUFFER_SIZE, BLOBPREFETCHSIZE, ROW_PREFETCH_SIZE) to 100.000.000 doesn't seem to have any effect.

I started to wonder if this is not a driver fault but an issue in Zeos...? As clearing LobCacheMode seems to fix it, maybe the it is somehow connected to TZCachedResultSet?
The main difference between the linked topic and this is the encoding - the cancellation exception is thrown even on AL32UTF8.

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 03.03.2023, 23:37
by aehimself
I can see lcmOnLoad handled in TZOracleRowAccessor (which is created by TZOracleCachedResultSet) so I attempted to get rid of creating a native TZCachedResultSet in TZAbstractOracleStatement.CreateResultSet... no success.
However, before receiving the "User requested to abort" exception, Delphi IDE reveals an access violation deep within oci.dll; call stack is something like...

Code: Select all

ZDbcOracleResultSet.TZOracleLobStream64.ReadPoll(' '#$AD'j')
ZDbcOracleResultSet.TZOracleRawMultibyteStream64.ReadStreamToMem($1D51186D920,33,$1D5177C13D0)
ZDbcResultSet.TZCodePageConversionStream.Create($1D5177C13D0,65001,65001,$1D511883410,$1D5178392E0)
ZDbcOracleResultSet.TZOracleRawMultibyteStream32.Create($1D5177C13D0,65001,65001,$1D511883410,$1D5178392E0,4)
ZDbcOracleResultSet.TZOracleClob.CreateLobStream(65001,lsmRead)
ZDbcResultSet.TZAbstractLob.GetStream(65001)
ZDbcResultSet.TZLocalMemCLob.CreateFromClob(TZOracleClob($1D5117F3BC0) as IZClob,65001,$1D512FB25C0,$1D517839A00)
ZDbcCache.SetAsCachedLob($1D51B3F48A3)
ZDbcCache.InternalSetLob($1D51B3F48A3,TZOracleResultSet($1D511827DD8) as IZResultSet,25)
ZDbcCache.TZRowAccessor.FillFromFromResultSet(TZOracleResultSet($1D511827DD8) as IZResultSet,$1D51784D760)
ZDbcOracleResultSet.TZOracleRowAccessor.FillFromFromResultSet(TZOracleResultSet($1D511827DD8) as IZResultSet,$1D51784D760)
ZDbcCachedResultSet.TZCachedResultSet.Fetch
ZDbcCachedResultSet.TZCachedResultSet.MoveAbsolute(1614)
ZDbcResultSet.TZAbstractResultSet.Next
ZAbstractRODataset.TZAbstractRODataset.FetchOneRow
ZAbstractRODataset.TZAbstractRODataset.FetchRows(0)
ZAbstractRODataset.TZAbstractRODataset.FetchAll
Digging further...

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 04.03.2023, 19:28
by aehimself
Confirmed in viewtopic.php?f=50&t=179882 that this is indeed a x64 issue.

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 30.11.2023, 22:17
by aehimself
So far all my attempts failed to pinpoint the root cause.

I guess they did change something between version 19 and later as the issue is still present in OCI 21.12.
LobCacheMode: OnLoad throws an instant nullpointer AV (at least in 64 bit) if the resultset has a CLOB field, even in AL32UTF8 codepage.

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 21.01.2024, 17:30
by aehimself
Bugtracker ticket created.

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 23.01.2024, 13:29
by MJFShark
I can't seem to replicate this issue with OCI 21.8. Anything else I can try to cause it?

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 23.01.2024, 20:46
by aehimself
It seems they cleared the CLOB fields during obfuscation / cloning.
I'm currently testing what value should go in there to trigger the error...

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 23.01.2024, 21:44
by MJFShark
I thought it was odd that those were empty. Note that I just tested with my own clob tables with no problems. If possible, when you create a new file, please use an Iso 8601 date format and UTF-8 for the encoding. That will just make it easier to load.

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 06.02.2024, 14:08
by aehimself
There are some updates on the bugracker, maybe the issue can be reproduced with the same dump as the UTF16. I'll send you a PM with the original CLOB values.

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 09.02.2024, 13:35
by MJFShark
Is anything else needed to create a clob that uses basicfile? Here's my create table statement which doesn't return an error, yet creates the clob as securefile.

Code: Select all

create table bstestbasic (
  id number(38) not null primary key,
  notes clob
)
lob (notes) store as basicfile;
select table_name, column_name, securefile from all_lobs where table_name = 'BSTESTBASIC'
This returns "SECUREFILE" = "YES" on my Oracle 19 cloud database.

-Mark

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 09.02.2024, 14:08
by aehimself
I don't know to be honest... I'm not that experienced with the inner life of RDBMS systems, I'm mostly their consumer :)
Our expert altered the table, maybe you can try that too?

ALTER TABLE mytable MOVE LOB(myfield) STORE AS SECUREFILE(TABLESPACE tablespace)

As Oracle advises you not to use BasicFile, is it possible that Oracle Cloud even denies it's usage...?

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 09.02.2024, 15:47
by MJFShark
> As Oracle advises you not to use BasicFile, is it possible that Oracle Cloud even denies it's usage...?

I think that's very possible! No luck with the alter command.

-Mark

Re: LobCacheMode: OnLoad, OCI 21.7 fails to load CLOBs

Posted: 18.04.2024, 18:18
by MJFShark
Revisiting this after my testing related to clob performance.

It seems that the combination of UTF8, Zeos' default blob prefetch size (8k), and cachemode of "onLoad" causes an access violation. Setting the prefetch to a size larger than any of your clobs causes empty strings to be returned (but doesn't AV.) Setting the lobprefetch to 0, using UTF16, or using "None" or "OnAccess" cachemodes fixes (or hides) the issue. I'm looking into this but if anyone has any thoughts, they'd be appreciated.

-Mark
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited