I am working on a kafka connect that get information from a database using an sql request. This sql request is like a
select
X,X,X,
JSON_ARRAYAGG(
JSON_OBJECT(
select X,X,X)).
My problem seems to be on the JSON_ARRAYAGG that can be over 18k caracters.
And in this case, if two of this kind of lines appear, the following erro is throw :
org.apache.kafka.connect.errors.DataException: java.sql?SQLException: ORA-22922: nonexistant LOB value at io.confluent.connect.jdbc.source.TimestampTableQuerier.doExtractRecord(TimestampTableQuerier.java:150) at ... Caused by: java;sql.SQLExeption: ORA-22922: nonexistant LOB value at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) at ...
I managed to have a similar result using DBeaver and with some help, it appear that unchecking the "Use Cache for CLOB value" solve the problem on the tool.
However, I don't manage to find on the jdbc documentation if there is a similar parameter that I can add to my kafka connect yaml to solve the issue.
Does anyone know if this parameter exists for Oracle jdbc?
What I already did : I managed to reduce the number of return values of the sql request in order to isolate two values that seems to generate the issue together. I managed to reproduce an error on DBeaver for these values ('IO error while copying content') and then a solution was explained to me for Dbeaver : Uncheck "Use Cache for CLOB value".
I tried to use blob and ask Kafka connect to transform it to text but the result is the same.
I found a solution to my problem even if it appears to be more of a workaround solution than a fix using jdbc parmeters.
The reason this error was triggers is because the my request using JSON_ARRAYAGG was a sub request in order to avec a null value instead of a [{}] when no data are retrieved.
The problem by using subrequest is the use of the cache.
My solution was then to remove most of the subrequests and manage the null value with a dbms_lob case when.