Error code is not set in exception object in cx_oracle 8.2+

[kotofos]

1. What versions are you using?

cx_oracle 8.3

platform.platform: Linux-5.3.18-59.37-preempt-x86_64-with-glibc2.31 sys.maxsize > 2**32: True platform.python_version: 3.9.8 cx_Oracle.version: 8.3.0 cx_Oracle.clientversion: (19, 6, 0, 0, 0)

Oracle server 19 EE docker image container-registry.oracle.com/database/enterprise:19.3.0.0

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

Error

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

Oracle's error code is not set in exception object. This makes harder to get the code value. Especially in sqlachemy cx_oracle dialect. It breaks reconnect workflow.

See sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle.is_disconnect

    def is_disconnect(self, e, connection, cursor):
        (error,) = e.args
        if isinstance(
            e, (self.dbapi.InterfaceError, self.dbapi.DatabaseError)
        ) and "not connected" in str(e):
            return True

        if hasattr(error, "code"):
            # ORA-00028: your session has been killed
            # ORA-03114: not connected to ORACLE
            # ORA-03113: end-of-file on communication channel
            # ORA-03135: connection lost contact
            # ORA-01033: ORACLE initialization or shutdown in progress
            # ORA-02396: exceeded maximum idle time, please connect again
            # TODO: Others ?
            return error.code in (28, 3114, 3113, 3135, 1033, 2396)
        else:
            return False

This behavior changed in cx_oracle 8.2. 8.1 is fine. bug report for sqlachemy https://github.com/sqlalchemy/sqlalchemy/issues/7748

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

from pprint import pprint

import cx_Oracle
from cx_Oracle import DatabaseError

def dump(obj):
    pprint(dict([attr, getattr(obj, attr)] for attr in dir(obj) if not attr.startswith('_')))

conn = cx_Oracle.connect(user='chinook', password='p4ssw0rd', dsn='127.0.0.1:1521/ORCLPDB1')
cur = conn.cursor()
cur.execute('select 1 from DUAL')

_ = input('Please restart server now, wait for startup and press enter')

try:
    cur.execute('select 1 from DUAL')
    print('Not failed, unexpected, please retry')
except DatabaseError as e:
    obj = e.args[0]
    dump(obj)

output

# cx oracle 8.3 
Please restart server now, wait for startup and press enter
{'code': 0, # code is not set
 'context': 'dpiStmt_execute: execute',
 'isrecoverable': False,
 'message': 'DPI-1080: connection was closed by ORA-3113',
 'offset': 14}

# 8.1 
{'code': 3113,
 'context': 'dpiStmt_execute: execute',
 'isrecoverable': True,
 'message': 'ORA-03113: end-of-file on communication channel\n'
            'Process ID: 698\n'
            'Session ID: 617 Serial number: 27273',
 'offset': 14}

[anthony-tuininga]

The behaviour you noted is intended. Instead of looking for a list of specific erorr codes (which can vary over time and may not be complete) the code was modified to raise a single exception (DPI-1080) which covers all of those scenarios (both now and in the future). It looks like SQLAlchemy has covered one case (DPI-1010: not connected) which is raised after the connection has already been closed (either explicitly by the user or because of connection failures). It should be adjusted to look for DPI-1080 as well (as I see is happening with the proposed patch).

Currently the error code is only populated when an Oracle error is generated. When a cx_Oracle or ODPI-C specific error message is raised the error code is not populated. If we could go back in time the error code would be a string (ORA-3113, DPI-1010, DPI-1080) but changing that now would be disruptive. Putting the DPI error code in there could be confusing as well since the error numbers could potentially overlap. So what we are doing in our own test suite is to look at the beginning of the message. The form is always XXX-NNNN: where XXX is one or ORA/DPI/etc. and NNNN is a number.

[cjbj]

I'll close this since SQLAlchemy has implemented the solution. Thanks for reporting the problem.

We have the addition of an attribute to show the prefix + code on our (long) TODO list.