Connection-pooling and CQN using a specific port for callbacks results in ORA-24911

[Newspaperman57]

1. What versions are you using?

   platform.platform: Linux-5.15.0-78-generic-x86_64-with-glibc2.36
   sys.maxsize > 2**32: True
   platform.python_version: 3.11.4
   oracledb.__version__: 1.3.2

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

An error: ORA-24911: Cannot start listener thread at specified port

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

When using CQN (Continous Query Notification) in connection pooling mode and setting a port in connection.subscribe(), i'm getting this traceback:

Traceback (most recent call last):
  File "/app/wbe_tms_api/wbe_tms_api.py", line 52, in <module>
    subscr = connection.subscribe(callback=cqn_callback,
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/oracledb/connection.py", line 801, in subscribe
    impl.subscribe(subscr, self._impl)
  File "src/oracledb/impl/thick/subscr.pyx", line 171, in oracledb.thick_impl.ThickSubscrImpl.subscribe
  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-24911: Cannot start listener thread at specified port

4. Does your application call init_oracle_client()?

Yes, using thick mode

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

import oracledb

def cqn_callback(message):
    logging.info("Notification:")
    for query in message.queries:
        for tab in query.tables:
            logging.info("Table:", tab.name)
            logging.info("Operation:", tab.operation)

oracledb.init_oracle_client()
dbpool = oracledb.create_pool(user="user", password="password", dsn="dsn", min=1, max=1, increment=0, expire_time=1, events=True)

with dbpool.acquire() as connection:
    subscr = connection.subscribe(callback=cqn_callback,
                                  operations=oracledb.OPCODE_ALLOPS,
                                  qos=oracledb.SUBSCR_QOS_BEST_EFFORT | oracledb.SUBSCR_QOS_ROWIDS,
                                  port=8134)
    subscr.registerquery(
        """
            SELECT deviceid, regnskab, afd, afsender, app_modul, dialog_id, ekstern_ref, emne, art, tekst, type, udfoert, status
            FROM TMS.WBE_TMS_BESKED
        """)
    logging.info("Registered query")
    time.sleep(10000000)

The following snippet using a simple connection instead is working as expected:

def cqn_callback(message):
    logging.info("Notification:")
    for query in message.queries:
        for tab in query.tables:
            logging.info("Table:", tab.name)
            logging.info("Operation:", tab.operation)

oracledb.init_oracle_client()
connection = oracledb.connect(user="user", password="password", dsn="dsn", events=True)
# dbpool = oracledb.create_pool(user="user", password="password", dsn="dsn", min=1, max=1, increment=0, expire_time=1, events=True)

# with dbpool.acquire() as connection:
subscr = connection.subscribe(callback=cqn_callback,
                              operations=oracledb.OPCODE_ALLOPS,
                              qos=oracledb.SUBSCR_QOS_BEST_EFFORT | oracledb.SUBSCR_QOS_ROWIDS,
                              port=8134)
subscr.registerquery(
    """
        SELECT deviceid, regnskab, afd, afsender, app_modul, dialog_id, ekstern_ref, emne, art, tekst, type, udfoert, status
        FROM TMS.WBE_TMS_BESKED
    """)
logging.info("Registered query")
time.sleep(10000000)

[anthony-tuininga]

I tried this with my own database and it worked just fine. You are missing the flag oracledb.SUBSCR_QOS_QUERY if you want to use query change notification (instead of database change notification). Can you clarify what Oracle environment you are using for both the client and the database?

[Newspaperman57]

Hi, sorry for the late response

I'm using instantclient-basic 12.2.0.1.0 on x86-64 to connect to "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" on a power8-based system.

[anthony-tuininga]

Hmmm, that strongly suggests that this is a limitation in the database or client software you are using -- they are quite old at this point! Are you able to try with a newer client/server combination?

[Newspaperman57]

I don't currently have access to a newer database, no. I guess we'll use a single connection for the CQN, it's no big deal :) Thank you anyway :) And thanks for the hint about oracledb.SUBSCR_QOS_QUERY. I'm new to CQN and you're right, i want query-level changes :)