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
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.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
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...
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.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
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.
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.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
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.
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.
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...?
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
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.
