cannot dispose of connection even if database session has been killed · Issue #67 · oracle/python-cx_Oracle (original) (raw)

given a test as follows:

import cx_Oracle

conn = cx_Oracle.connect(
    user="scott",
    password="tiger",
    dsn=cx_Oracle.makedsn(
        "192.168.1.185", 1521, sid="xe",
    )
)

cursor = conn.cursor()
cursor.execute("select 1 from dual")

# run alter system kill session '<sid>, <session#>' here'
raw_input("stop the database, or kill the session, etc, press enter")

c2 = conn.cursor()

try:
    c2.execute("select 2 from data")
except cx_Oracle.DatabaseError as err:
    # cx_Oracle.DatabaseError: ORA-00028: your session has been killed
    print err

    # our session has been killed.  We have a dead socket in our application,
    # we need to clean it up unconditionally.

    # fails
    conn.close()

Given "Prevent closing the connection when there are any open statements or LOBs and add new error “DPI-1054: connection cannot be closed when open statements or LOBs exist”" in cx_Oracle 6.0, need to know the best practice in this situation. I understand the upstream Oracle OCI has added this but from a Python / scrpting language / humane POV this means I have to make sure all cursors are tracked at all times and also closed. this seems like something that could really easily be part of cx_Oracle, enabled by a flag on connect "auto-clean cursors", or if I could register event hooks to do this, it seems like there's a Subscription system now but it's not clear that it works for this.