python-cx_Oracle ERROR appears on oracledb version 3.2.0

[avmcohen]

We had an issue with fetch >1GB size data in execution.
It was solved in oracledb version 2.5.1, currently like to upgrade to 3.2.0, and seems we have the issue again:

Here's the test case with all steps:

dbsTL = ora.connect("DSTDxx/xxxx@DSxxxx")
ora.version
'3.2.0'
dbc1 = dbsTL.cursor()
qry = "select DT_GROUP, RELEASE_VERSION, BS_DATA from ETL_CONTROL"
ins = "insert into ETL_CLUM (DT_GROUP, RELEASE_VERSION, BS_DATA) values (:B1,:B2,:B3)"
dbc1.execute(qry)
<oracledb.Cursor on <oracledb.Connection to DSTDB@DSxxxx>>
ds = dbc1.fetchmany(32)
>>> dbc1.executemany(ins, ds)
Traceback (most recent call last):
File "", line 1, in
File "/usr/local/pyenv/versions/3.9.5/lib/python3.9/site-packages/oracledb/cursor.py", line 755, in executemany
num_execs = self._impl._prepare_for_executemany(
File "src/oracledb/impl/base/cursor.pyx", line 443, in oracledb.base_impl.BaseCursorImpl._prepare_for_executemany
File "src/oracledb/impl/base/cursor.pyx", line 498, in oracledb.base_impl.BaseCursorImpl.bind_many
File "src/oracledb/impl/base/cursor.pyx", line 61, in oracledb.base_impl.BaseCursorImpl._bind_values
File "src/oracledb/impl/base/cursor.pyx", line 122, in oracledb.base_impl.BaseCursorImpl._bind_values_by_position
File "src/oracledb/impl/base/bind_var.pyx", line 136, in oracledb.base_impl.BindVar._set_by_value
File "src/oracledb/impl/base/var.pyx", line 86, in oracledb.base_impl.BaseVarImpl._check_and_set_value
File "src/oracledb/impl/base/var.pyx", line 71, in oracledb.base_impl.BaseVarImpl._check_and_set_scalar_value
File "src/oracledb/impl/thick/var.pyx", line 333, in oracledb.thick_impl.ThickVarImpl._set_scalar_value
File "src/oracledb/impl/thick/utils.pyx", line 484, in oracledb.thick_impl._raise_from_odpi
File "src/oracledb/impl/thick/utils.pyx", line 474, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: DPI-1057: buffer size of 1513242692 is too large (max 1073741822)

[cjbj]

Can you give a complete testcase?

Is the fetchmany() returning Lobs or strings? i.e what's the value of oracledb.defaults.fetch_lobs or equivalent handler.

What's types are the columns?

How big is the data?

What are the client & DB versions? See the bug issue template

[avmcohen]

Your right - here some more notes:

• Forgot to note that this working fine in THIN mode. fail only on THICK mode.
• The big column is BLOB (BS_DATA)
• Using OutputTypeHandler as:
• if defaultType == ora.DB_TYPE_BLOB:
  return cursor.var(ora.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)
• Data is ~1.5GB
• Oracle version: 19.27.0.0.0 (client: 19.0.0.0.0)
  Thanks

[avmcohen]

(19, 27, 0, 0, 0)

[anthony-tuininga]

Is is just one row of data? Multiple rows of data? What is the arraysize? Do you have the same issue if you set oracledb.defaults.fetch_lobs = False instead of the using the output type handler?

[avmcohen]

It's a BLOB data. Note that it was an issue with cx_Oracle, solved in oracledb 2.5.1 and now I tried to upgrade to 3.2.0 - and got the error.
(Only in THICK mode)

[cjbj]

How many rows does the query return? Ideally you would construct a full testcase that runs (including SQL to create the table and data used). Otherwise we may not be able to reproduce your problem, and won't be able to fix it.

[avmcohen]

It failed on one record which the size of BLOB exceed 1GB:

Table has 2 dummy column + 1 BLOB data. once I am trying to insert fetched 1.5GB record, got the error.

[avmcohen]

Following is the full testing case:

python 3.9.5 , oracledb 3.2.0

• import os
• import oracledb as ora
• ora.init_oracle_client() # set THICK mode (THIN mode works fine)
• bDATA = os.urandom(int(1.5 * 1024 * 1024 * 1024)) # Generate 1G+ binary data
• DBS = ora.connect(“User/Pass@DB”)
• dbc = DBS.cursor()
• dbc.execute("CREATE TABLE B_TABLE ( BS_DATA BLOB)") # Create BLOB table
• dbc.execute("insert into B_TABLE (BS_DATA) values(:b1)", (bDATA,) )

Getting the ERROR:
_Traceback (most recent call last):
File "", line 1, in
File "/usr/local/pyenv/versions/3.9.5/lib/python3.9/site-packages/oracledb/cursor.py", line 708, in execute
self._prepare_for_execute(statement, parameters, keyword_parameters)
File "/usr/local/pyenv/versions/3.9.5/lib/python3.9/site-packages/oracledb/cursor.py", line 137, in _prepare_for_execute
self._impl._prepare_for_execute(
File "src/oracledb/impl/base/cursor.pyx", line 409, in oracledb.base_impl.BaseCursorImpl._prepare_for_execute
File "src/oracledb/impl/base/cursor.pyx", line 509, in oracledb.base_impl.BaseCursorImpl.bind_one
File "src/oracledb/impl/base/cursor.pyx", line 66, in oracledb.base_impl.BaseCursorImpl._bind_values
File "src/oracledb/impl/base/cursor.pyx", line 127, in oracledb.base_impl.BaseCursorImpl._bind_values_by_position
File "src/oracledb/impl/base/bind_var.pyx", line 161, in oracledb.base_impl.BindVar._set_by_value
File "src/oracledb/impl/base/var.pyx", line 87, in oracledb.base_impl.BaseVarImpl._check_and_set_value
File "src/oracledb/impl/base/var.pyx", line 72, in oracledb.base_impl.BaseVarImpl._check_and_set_scalar_value
File "src/oracledb/impl/thick/var.pyx", line 325, in oracledb.thick_impl.ThickVarImpl._set_scalar_value
File "src/oracledb/impl/thick/utils.pyx", line 456, in oracledb.thick_impl._raise_from_odpi
File "src/oracledb/impl/thick/utils.pyx", line 446, in oracledb.thick_impl.raise_from_info
oracledb.exceptions.DatabaseError: DPI-1057: buffer size of 1610612736 is too large (max 1073741822)

[cjbj]

I created #519 for this.