Streaming LOBs, writing over 1gb of data to table CLOB column with JSON constraint?

[Christopher-Gentle]

Hello,

Using Oracle 19c,
Is it possible to stream data from a >1GB JSON file to a table with a CLOB column that has a JSON check constraint?

I looked at the example below, it seems they do not have the JSON check constraint. I tried to adapt it to my requirement, but it fails with a JSON validation error.

id_val = 9
lob_var = cursor.var(oracledb.DB_TYPE_BLOB)
cursor.execute("""
        insert into lob_tbl (id, b)
        values (:1, empty_blob())
        returning b into :2""", [id_val, lob_var])
blob, = lobVar.getvalue()
offset = 1
num_bytes_in_chunk = 65536
with open("image.png", "rb") as f:
    while True:
        data = f.read(num_bytes_in_chunk)
        if data:
            blob.write(data, offset)
        if len(data) < num_bytes_in_chunk:
            break
        offset += len(data)
connection.commit()

[anthony-tuininga]

Interesting! Can you share the code you actually used that got the JSON validation error? Specifically I'm interested to know when you get the error.

[Christopher-Gentle]

yes. I have tested it with both large and small datasets.
The only way it will work like this is if I disable/remove the constraint.

If I was going to go that route -- I would just invoke sqlldr (constraints have to be disabled to load JSON anyway).
I was hoping to centralize all of my Oracle operations in cx_Oracle.

[anthony-tuininga]

Thanks for the confirmation. This may be a bug. Can you indicate which server version you are using? Also, do you get the same results in both thin and thick modes?

[Christopher-Gentle]

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
cx_Oracle version 8.3.0

I have not tried to switch between modes, let me look into that.
Thanks!

[Christopher-Gentle]

I tried both modes, no luck.
It doesn't even like the constraint being disabled. It has to be completely removed.

[Christopher-Gentle]

I did a bit more digging, seems to be an Oracle bug and not a cx_Oracle bug. Once you add a IS_JSON constraint, even if it's disabled, it will throw a JSON syntax error when trying to use DBMS_LOB.write IF any chunk of data is not completely intact JSON. The only work-around I have found is 1) removing the constraint, 2) disable constraint, use sqlldr to load the data, finally, enable the constraint. I'm curious what sqlldr is using if not DBMS_LOB.