Getting "cx_Oracle.DatabaseError: DPI-1037" when trying to query a long view definition as STRING

[recognosco]

1. What versions are you using?

Database: 19.16.0.0.0
platform.platform: Linux-3.10.0-1160.76.1-el7.x86_64-x86_64-with-glibc2.17
sys.maxsize > 2**32: True
platform.python_version: 3.9.13
cx_Oracle.version: 8.1.0
cx_Oracle.clientversion: (18, 3, 0, 0, 0)

2. Is it an error or a hang or a crash? It's an error that is returned from the Database.

3. What error(s) or behavior you are seeing? cx_Oracle.DatabaseError: DPI-1037: column at array position 0 fetched with error 1406 We have noticed a case where cx_Oracle is raising an error when the cursor.outputtypehandler is set with defaultType as cx_Oracle.STRING and works when it is set with cx_Oracle.LONG_STRING. The issue manifested in our codebase from a SQLAlchemy change, where setting coerce_to_unicode was causing the defaultType to change. Despite the change in defaultType, should this error manifest? We would expect cx_Oracle to handle it gracefully and show us the query result.

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

def cx_conn():
    import cx_Oracle
    cx_dsn= cx_Oracle.makedsn("hostname", "12345", service_name="SERVICE_NAME")
    connection = cx_Oracle.connect(user="username", password="password1234", dsn=cx_dsn)
    cursor = connection.cursor()
    cursor.arraysize = 500
    def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
        # return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize) # works
        return cursor.var(cx_Oracle.STRING, arraysize=cursor.arraysize)        # doesn't work
    cursor.outputtypehandler = OutputTypeHandler
    view_name = 'VW_TEST_CX_ORACLE'
    view_def = f"CREATE OR REPLACE VIEW {view_name} AS SELECT '{'A' * 4000}' AS a, '{'B' * 4000}' AS b, '{'C' * 4000}' AS c, '{'D' * 3950}' AS d FROM DUAL"
    cursor.execute(view_def)
    print(f'''This creates a long view definition. It is {len(view_def)} characters long.
        This is the threshold where "sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1037: column at array position 0 fetched with error 1406" error starts.
        Our production view definitions are often much longer''')
    test_cmd = f"SELECT text FROM all_views WHERE view_name='{view_name}'"
    result = cursor.execute(test_cmd)
    x = result.fetchone()
    print(x)

[anthony-tuininga]

I don't believe this is a bug. The type STRING is limited in Oracle to either 4000 bytes or 32767 bytes (depending on the configuration of your database). In addition, the Oracle client libraries specify that you must provide a length and this amount of memory will be allocated. The type LONG_STRING is handled a bit differently as the length is not known up front and memory is allocated dynamically as required. cx_Oracle does this transition automatically if the size detected exceeds 32767 -- so you would need to specify a size greater than that amount or explicitly pick the type LONG_STRING.

The new driver (python-oracledb) has a thin mode which is not restricted in this fashion. It does not require the size and everything is done dynamically. As a bonus it also outperforms thick mode (and cx_Oracle) under most cicrumstances (but it doesn't implement everything that thick mode does yet). SQLAlchemy is known to work with python-oracledb and version 2.0 (in beta right now) supports it explicitly. You can use this technique with version 1.4.

[cjbj]

@recognosco see the release announcement for information about cx_Oracle and python-oracledb.

[cjbj]

@recognosco can you report the coerce_to_unicode type behavior change to the SQLAlchemy repo?

Also you might be interested in https://medium.com/gitconnected/using-python-oracledb-1-0-with-sqlalchemy-pandas-django-and-flask-5d84e910cb19

[anthony-tuininga]

Which coerce_to_unicode change are you referring to anyway? Mike Bayer worked with me on the more recent changes and they should be fine as far as I know! More details would definitely be helpful if you want additional guidance.

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not been updated for a month.

[DartVeDroid]

I second this.

Happens when I call sqlalchemy's inspector.get_view_definition() (only for some views), and disappears when I set coerce_to_unicode=False.

Can't provide any detailed info, though.

Windows, cx_Oracle v.8.3.0.

[cjbj]

@DartVeDroid it's time to upgrade from 8.3 to the latest version, which was renamed to python-oracledb. See the release announcement. Also see:

• https://medium.com/gitconnected/using-python-oracledb-1-0-with-sqlalchemy-pandas-django-and-flask-5d84e910cb19
• https://medium.com/oracledevs/using-the-development-branch-of-sqlalchemy-2-0-with-python-oracledb-d6e89090899c