Why is the outconverter not called for byte conversions in oracledb/cx_Oracle (Python)?

Question

I am trying to fetch SDO_GEOMETRY typed columns from an Oracle database using Python (3.11) and the oracledb library (1.3.0). I want to use an outputtypehandler to convert the SDO_GEOMETRY instances into pickle encoded bytes. This works fine for NUMBER columns, if I try to set the typ parameter in cursor.var to typ=str, but fails for typ=bytes and typ=oracledb.DB_TYPE_RAW for all kinds of column types. The SDO_GEOMETRY columns always produce errors regardless of the typ parameter value. The outconverter is not even called as shown below.

Here is my example code:

import oracledb
import pickle


def output_type_handler(cursor, name, default_type, size, precision, scale):

    def pickle_converter(obj) -> bytes:
        print(f"Converter called for {name}.")
        return pickle.dumps(obj)

    if default_type == oracledb.DB_TYPE_OBJECT:
        return cursor.var(
            typ=oracledb.DB_TYPE_RAW, 
            size=size, 
            arraysize=cursor.arraysize, 
            outconverter=pickle_converter
        )

# Switch to thick mode
oracledb.init_oracle_client()

ora_connection = oracledb.connect(
    dsn=oracledb.makedsn("ora.local", 1521, "TST"),
    user="test",
    password="test"
)

ora_connection.outputtypehandler = output_type_handler

with ora_connection.cursor() as cursor:
    # GEOMETRIE is an SDO_GEOMETRY column
    recs = cursor.execute("SELECT GEOMETRIE FROM MV_CS_STWG1KP").fetchmany(5)
    print(recs)

Output (note, that the line Converter called for ... is not even printed so the converter was never called):

Traceback (most recent call last):
  File "/home/jannis/.config/JetBrains/PyCharmCE2023.1/scratches/tmp.py", line 28, in <module>
    num_recs = cursor.execute("SELECT GEOMETRIE FROM MV_CS_STWG1KP").fetchmany(5)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jannis/PycharmProjects/etl_engine/venv/lib/python3.11/site-packages/oracledb/cursor.py", line 492, in fetchmany
    row = fetch_next_row(self)
          ^^^^^^^^^^^^^^^^^^^^
  File "src/oracledb/impl/base/cursor.pyx", line 397, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
  File "src/oracledb/impl/thick/cursor.pyx", line 132, in oracledb.thick_impl.ThickCursorImpl._fetch_rows
  File "src/oracledb/impl/thick/utils.pyx", line 413, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 403, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-00932: inconsistent datatypes: expected BINARY got ADT

I must use thick mode to connect to an older Oracle database. How can I solve this?

SDO is a custom object types, but you try to bind a binary data to it. [oracledb.DB_TYPE_RAW](https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb.DB_TYPE_RAW): *Describes columns, attributes or array elements in a database that are of type RAW* Try to set the type to `oracledb.DB_TYPE_OBJECT` or `Connection.gettype("SDO_GEOMETRY")` — astentx, Apr 14 '23 at 11:10
This does not work either. DB_TYPE_OBJECT provokes an error (DPY-2037: no object type specified for object variable) and Connection.gettype is a bound method, that I cannot reference inside the output_type_handler function without an instance of Connection.... I need a binary representation of the Geo object as an output — Jannis, Apr 14 '23 at 11:28

Christopher Jones · Accepted Answer · 2023-04-15T06:46:13.377

You need to convert to a Python object before serializing it. Even removing your output handler and pickling explicitly gives an error:

cur.execute("select geometry from testgeometry")
r, = cur.fetchone()
p = pickle.dumps(r) # fails with error "KeyError: '__getstate__'"

Instead try the following. It uses a type converter to convert to a Python object, and then a row factory to pickle this.

class mySDO(object):
    def __init__(self, gtype, elemInfo, ordinates):
        self.gtype = gtype
        self.elemInfo = elemInfo
        self.ordinates = ordinates

obj_type = con.gettype("MDSYS.SDO_GEOMETRY")

def SDOOutputTypeHandler(cursor, name, default_type, size, precision, scale):
    def SDOOutConverter(DBobj):
        return mySDO(int(DBobj.SDO_GTYPE), DBobj.SDO_ELEM_INFO.aslist(), DBobj.SDO_ORDINATES.aslist())

    if default_type == oracledb.DB_TYPE_OBJECT:
        return cursor.var(obj_type, arraysize=cursor.arraysize, outconverter=SDOOutConverter)

cur.outputtypehandler = SDOOutputTypeHandler

cur.execute("select geometry from testgeometry")
cur.rowfactory = lambda *args: pickle.dumps(args)
p = cur.fetchone()
print(p)

However, would it be better to use the 'well known binary' (WKB) format for spatial objects? You can get this directly from the DB without needing output converters or row factories:

oracledb.defaults.fetch_lobs = False

cur = con.cursor()
cur.execute("select sdo_util.to_wkbgeometry(geometry) from testgeometry")
b = cur.fetchone()
print(b)

Tank you! That did it. The WKB solution is probably the best way to do it. — Jannis, Apr 15 '23 at 08:17
For reference, another variant of the type handler/outconverter/rowfactory code is in https://github.com/oracle/python-cx_Oracle/issues/146#issuecomment-378600026 — Christopher Jones, Apr 20 '23 at 02:25

Why is the outconverter not called for byte conversions in oracledb/cx_Oracle (Python)?

1 Answers1