Python oracledb continous query notification not sending messages from database

Question

I've been trying to use Continuous Query Notification (CQN) in python script to get notification from database about changes that were made to a specific table.

I have followed tutorial from this link here https://python-oracledb.readthedocs.io/en/latest/user_guide/cqn.html

Connection to oracle database was successful and I can query the table to get results but I can't get any message from callback function that looks like this

def cqn_callback(message):
    print("Notification:")
    for query in message.queries:
        for tab in query.tables:
            print("Table:", tab.name)
            print("Operation:", tab.operation)
            for row in tab.rows:
                if row.operation & oracledb.OPCODE_INSERT:
                    print("INSERT of rowid:", row.rowid)
                if row.operation & oracledb.OPCODE_DELETE:
                    print("DELETE of rowid:", row.rowid)

subscr = connection.subscribe(callback=cqn_callback,
                              operations=oracledb.OPCODE_INSERT | oracledb.OPCODE_DELETE,
                              qos=oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS)
subscr.registerquery("select * from regions")
input("Hit enter to stop CQN demo\n")

I can see that the registration was created in the database after I run the script but I just don't receive any message about insert or delete after I perform any of those operations through SQL* Plus or SQL Developer.

I am reading other questions and blogs about this functionality but currently without success, so if anyone has any recommendations or has encountered similar problem, please comment or answer here.

Oracle database 12C from docker

Python version is 3.10.7

I am running it in thick mode and for oracle client libraries I am using this command

oracledb.init_oracle_client(lib_dir = ".../instantclient_21_3"

P.S This is my first time posting a question here so if I didn't correctly follow a structure or rules of asking a question please correct me, thanks in advance :)

score 1 · Accepted Answer · answered Nov 10 '22 at 19:01

Please take a look at the requirements for CQN in the documentation. Note in particular the fact that the database needs to connect back to the application. If this cannot be done no notifications will take place even though the registration is successful with the database. With Oracle Database 19.4 a new mode was introduced which eliminates this requirement, but since you are still using 12c that won't work for you. You will need to ensure that the database can connect back to the application -- opening up any ports, ensuring that an IP address is directly specified in the parameters or an IP address can be looked up from the name of the client machine connecting to the database, etc.

Thanks, setting up an ip address and port did it. Just in case anyone wants to see how it solved my issue, I did this `subscr = connection.subscribe(callback=cqn_callback, operations=oracledb.OPCODE_INSERT | oracledb.OPCODE_DELETE, qos=oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS, ip_address=*insert my ip address here*, port = 1521 )` — Filip, Nov 11 '22 at 02:03

Python oracledb continous query notification not sending messages from database

1 Answers1