binding bytes with dbms_lob.compare

[rob975]

My current code binds bytes objects to insert values into a column of type blob.
According to documentation this is fine for sizes up to 1 GB.

Now I need to update the value if it's changed and I use dbms_lob.compare to test changes.
Using bytes as binding variable ORA-01460 is raised if the size is greater than 32767.
No error is raised if the binding variable is created using cursor.var.
There is no difference between thin and thick mode.

I'm wondering if this is the expected behavior or I'm doing something wrong.
Below is a test table, sample code and the output, including version of db and libraries.

create table test_blob (test_id number, test_data blob);

import os import platform import sys import oracledb print("platform.platform:", platform.platform()) print("sys.maxsize > 2**32:", sys.maxsize > 2**32) print("platform.python_version:", platform.python_version()) print("oracledb.version:", oracledb.version) if os.environ.get("THICK"): oracledb.init_oracle_client() print("client version:", oracledb.clientversion()) conn = oracledb.connect(os.environ["DSN"]) print("database version:", conn.version) insert = "insert into test_blob values (:id, :data)" compare = "select dbms_lob.compare(test_data, :data) from test_blob where test_id = :id" size = 32768 data = b"0" * size cur = conn.cursor() cur.execute(insert, {"id": 1, "data": data}) try: cur.execute(compare, {"id": 1, "data": data}) except oracledb.DatabaseError as ex: errmsg = str(ex).splitlines()[0] print(f"Comparison error: {errmsg}") var = cur.var(oracledb.DB_TYPE_BLOB) var.setvalue(0, data) cur.execute(compare, {"id": 1, "data": var}) print(f"Comparison result: {cur.fetchone()[0]}") data = b"0" * 32767 cur.execute(compare, {"id": 1, "data": data}) print(f"Comparison result: {cur.fetchone()[0]}")

platform.platform: Linux-6.10.12-200.fc40.x86_64-x86_64-with-glibc2.39 sys.maxsize > 2**32: True platform.python_version: 3.9.20 oracledb.version: 2.4.1 database version: 23.5.0.24.7 Comparison error: ORA-01460: unimplemented or unreasonable conversion requested Comparison result: 0 Comparison result: 1

[anthony-tuininga]

Yes, this is expected behavior. If you want to compare values beyond 32767 bytes you will have to use a temporary LOB created with connection.createlob() or using a variable of type oracledb.DB_TYPE_BLOB. Oracle Database limits strings to 32767 bytes.