CQN notifications are not received / callback not firing

[Sjoerd82]

1. What versions are you using?

Give your database version. Oracle 19c Oracle InstantClient 19.14

Python/System:

>>> print("platform.platform:", platform.platform())
platform.platform: Windows-2012ServerR2-6.3.9600-SP0

>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True

>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.8.2

Cx_oracle:

>>> print("cx_Oracle.version:", cx_Oracle.version)
cx_Oracle.version: 8.3.0

>>> print("cx_Oracle.clientversion:", cx_Oracle.clientversion())
cx_Oracle.clientversion: (19, 14, 0, 0, 0)

Additionally: SELECT VALUE FROM v$parameter where name='job_queue_processes'; -- 320

2. Describe the problem Registrations are successfully created, query is registered, but notifications are not received back in Python.

SELECT * FROM SYS.USER_CHANGE_NOTIFICATION_REGS;
1529    4   OCI:EP:1528 14  0   88  REP_BV_STR.TEST_SVE

SELECT * FROM SYS.DBA_CQ_NOTIFICATION_QUERIES;
49   SELECT REP_BV_STR.TEST_SVE.TST_ID , REP_BV_STR.TEST_SVE.TESTJE  FROM REP_BV_STR.TEST_SVE   1529    TAB76465

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

SQL:

CREATE TABLE rep_bv_str.TEST_SVE (
  tst_id INTEGER PRIMARY KEY,
  testje VARCHAR2(50)
);
insert into rep_bv_str.test_sve values (1,'foo');
insert into rep_bv_str.test_sve values (2,'foo');

Python:

import time
import cx_Oracle as oracledb

registered = True

def callback(message):
    global registered
    print("callback!")
    if not message.registered:
        print("Deregistration has taken place...")
        registered = False
        return
    connection = pool.acquire()
    for query in message.queries:
        for table in query.tables:
            if table.rows is None:
                print("Too many row changes detected in table", table.name)
                continue
            num_rows_deleted = 0
            print(len(table.rows), "row changes detected in table", table.name)
            for row in table.rows:
                if row.operation & oracledb.OPCODE_DELETE:
                    num_rows_deleted += 1
                    continue
                ops = []
                if row.operation & oracledb.OPCODE_INSERT:
                    ops.append("inserted")
                if row.operation & oracledb.OPCODE_UPDATE:
                    ops.append("updated")
                cursor = connection.cursor()
                cursor.execute("""
                        select IntCol
                        from TestTempTable
                        where rowid = :rid""",
                        rid=row.rowid)
                int_col, = cursor.fetchone()
                print("    Row with IntCol", int_col, "was", " and ".join(ops))
            if num_rows_deleted > 0:
                print("   ", num_rows_deleted, "rows deleted")
            print("=" * 60)

pool = oracledb.SessionPool(user='TAB76465',
                            password='REDACTED',
                            dsn='BVIBV_REP', min=2, max=5,
                            increment=1, events=True)

with pool.acquire() as connection:
    qos = oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS
    ops = oracledb.OPCODE_INSERT | oracledb.OPCODE_UPDATE | oracledb.OPCODE_DELETE
    sub = connection.subscribe(callback=callback, timeout=100, qos=qos, operations=ops, clientInitiated=True) #, qos=qos client_initiated clientInitiated
    print("Subscription created with ID:", sub.id)
    query_id = sub.registerquery("SELECT * FROM rep_bv_str.test_sve")
    print("Registered query with ID:", query_id)

while registered:
    print("Waiting for notifications....")
    time.sleep(5)

To trigger:

insert into rep_bv_str.test_sve values (3,'foo');
update rep_bv_str.test_sve set testje='bar' where tst_id=2;
delete from rep_bv_str.test_sve where tst_id=1;
commit;

The script starts and enters the waiting loop, the callback is never called. The script uses a regular (non-SYS) database user, which has a GRANT EXECUTE ON DBMS_CQ_NOTIFICATION.

[cjbj]

With a minor change of schema and to the validation query table called in the callback ( TestTempTable -> TEST_SVE) this is what I get on macOS with 19.8 Instant Client to a 21c DB on Linux:

$ python issue625.py 
Subscription created with ID: 910
Registered query with ID: 41
Waiting for notifications....
Waiting for notifications....
callback!
1 row changes detected in table CJ.TEST_SVE
    Row with IntCol 3 was inserted
============================================================

If I change the triggering DELETE to delete the inserted row the callback also gets called:

callback!
1 row changes detected in table CJ.TEST_SVE
    1 rows deleted

[Sjoerd82]

Ok thanks for that! At least that tells me the issue is probably not in the code. Not being a DBA myself, is there something I could ask our DBA to check? Does Oracle track errors in relation to CQN somewhere?

Could there perhaps somewhere (client/server/...) be a driver issue? I've heard that certain jdbc drivers don't support CQN (not sure if that's true, or which drivers that concerns). The DBA reports that "at least there was no dump"...

[cjbj]

I'll ask the CQN team if they can comment.

In the meantime, is there anything about the connection or DB that might be 'special'? Cloud? TLS or native network encryption?

[Sjoerd82]

We use proxy accounts, which might be considered 'special'. It's not a local database, but it's not cloud either. There might be TLS or network encryption active, I will check that. In the meantime, we're also going to raise a ticket with Oracle. If all fails, we still can poll every 5 sec, but I would consider that a defeat ;-). Thanks for the assistance thusfar!

[cjbj]

@Sjoerd82 email me ticket number. See my profile for the email address.

[Sjoerd82]

@Sjoerd82 email me ticket number. See my profile for the email address.

And I just received it and forwarded it to you.

[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.