Performance issue dealing with CLOB

[crackman77]

Context :
Python 64bits 3.12.7
oracledb==2.5.1
Oracle : 19.18
Run in thick mode

Hello,

I have a script that reads a Parquet file and integrates it into a database. One of the columns contains geospatial data in WKT format. The geometries are "complex" (i.e., they can have tens of thousands of vertices).

I am trying to integrate these data using Oracle's SDO_GEOMETRY constructor, but my query is limited for character strings exceeding 32,768 characters (Error ORA-01461: can bind a LONG value only for insert into a LONG column if the text exceeds this limit).

To bypass this, I used the setinputsizes method to type the column as oracledb.DB_TYPE_CLOB. This works, but performance is significantly degraded compared to integrating data with oracledb.DB_TYPE_VARCHAR (which I use for lighter geometries). Aroud 30-50x more time.

Is there any workaround I could do to speed up my integration?
Some configuration on server-side?

Regards,
Marc

[anthony-tuininga]

PL/SQL has a hard limit of 32,767 bytes for all parameters. There is nothing that you can do about that! And unfortunately, using CLOB requires the use of temporary LOBs, which are indeed very slow since they involve multiple round trips.

Since a CLOB is necessary for any data exceeding 32,767 bytes the only workaround I can think of is to create a global temporary table with a CLOB column and write to it using executemany() with oracledb.DB_TYPE_VARCHAR as the type. This should be very fast. Then process the global temporary table on the server using SQL or PL/SQL as needed.

[crackman77]

Thanks Anthony.