Setting undecodable bytes on an Object Type no longer works in Oracledb, but it worked in cx_Oracle

[mkmoisen]

cx_Oracle and oracledb have different behavior when setting undecodable bytes data to an object type.

obj = mytype.newobject()
obj.DATA = b'\x1f\x8b\x08\x00\xa7'

In cx_Oracle, this will not raise an error, and will save the bytes to Oracle correctly.

In oracledb, this raises a UnicodeDecodeError

---

My use case is that I am logging http requests to an Oracle table. Some requests contain Excel uploads. The bytes for an Excel file are not decodable into the str type.

I have a package with a record type, collection of that record, and a procedure to save the http logs in bulk.

Setting an attribute on the record type to the Excel bytes works on cx_Oracle, and saves to the database correctly.

In oracledb, it doesn't work.

---

Here is a minimum reproducable example.

CREATE PACKAGE MYPACKAGE
IS
    TYPE MY_RECORD_TYPE IS RECORD (
        data VARCHAR2(4000)
    );
END MYPACKAGE;

import cx_Oracle
import oracledb

oracledb.init_oracle_client()


# These bytes can be found in the start of any Excel and cannot be decoded to str
bad_bytes = b'\x1f\x8b\x08\x00\xa7'

ORACLE_DSN = 'scott/tiger@local'
PLSQL_RECORD_TYPE = 'MYSCHEMA.MYPACKAGE.MY_RECORD_TYPE'


# It works fine with cx_Oracle
cx_conn = cx_Oracle.connect(ORACLE_DSN)
cx_type = cx_conn.gettype(PLSQL_RECORD_TYPE)
cx_rec = cx_type.newobject()

# This works
cx_rec.DATA = bad_bytes

# It fails with oracledb in thick mode
od_conn = oracledb.connect(ORACLE_DSN)
od_type = od_conn.gettype(PLSQL_RECORD_TYPE)
od_rec = od_type.newobject()

od_rec.DATA = bad_bytes

---

1. What versions are you using?

   oracledb.version
   '1.2.1'

Give your database version.

19c

Also run Python and show the output of:

>>> print("platform.platform:", platform.platform())
platform.platform: Linux-5.10.102.1-microsoft-standard-WSL2-x86_64-with-glibc2.28

>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True

>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.10.9

>>> oracledb.__version__
'1.2.1'

2. Is it an error or a hang or a crash?

Error

4. What error(s) or behavior you are seeing?

>>> orr.REQUEST_DATA = bad_bytes
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/app/project/lib/python3.10/site-packages/oracledb/dbobject.py", line 54, in __setattr__
    self._impl.set_attr_value(attr_impl, value)
  File "src/oracledb/impl/base/dbobject.pyx", line 94, in oracledb.base_impl.BaseDbObjectImpl.set_attr_value
  File "src/oracledb/impl/base/connection.pyx", line 76, in oracledb.base_impl.BaseConnImpl._check_value
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x8b in position 1: invalid start byte

5. Does your application call init_oracle_client()?

Yes, thick mode.

6. Include a runnable Python script that shows the problem.

See above.

Include all SQL needed to create the database schema.

Format code by using three backticks on a line before and after code snippets, for example:

[mkmoisen]

I believe this is caused here:

https://github.com/oracle/python-oracledb/blob/main/src/oracledb/impl/base/connection.pyx#L76

elif db_type_num in (DB_TYPE_NUM_CHAR,
                             DB_TYPE_NUM_VARCHAR,
                             DB_TYPE_NUM_NCHAR,
                             DB_TYPE_NUM_NVARCHAR,
                             DB_TYPE_NUM_LONG_VARCHAR,
                             DB_TYPE_NUM_LONG_NVARCHAR):
            if isinstance(value, bytes):
                return (<bytes> value).decode()

A VARCHAR2 column in Oracle can store bytes. Even completely invalid bytes that cannot be human readable.

[anthony-tuininga]

This is not a bug. :-) The database is not supposed to be able to store data in VARCHAR2 or CHAR columns/attributes that is not encodable as a string in the database character set. As noted, there is a way to do so today. So long as the client character set and the database character set are identical the server doesn't check. You should be using RAW columns/attributes instead if you intend to store data that is not valid strings.

Since this is relatively common there is a "bypass_decode" option to use when fetching data. There is no such option currently available for object attributes. If using RAW columns/attributes is not an option for you, you can request that this be turned into an enhancement request for a "bypass_decode" option to be available on objects, too.

[cjbj]

Closing. I think this has run its course.