cx_Oracle & event notifications: ORA-29972: user does not have privilege to change/ create registrat

Andrey Komrakov

Hi,

I'm trying to set up DCN events from the python script, using cx_Oracle.  I'm getting the following error when trying to subscribe to table changes:

subscriptionInsDel = con.subscribe(callback = DCNCallback, operations = cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE, rowids = True)

cx_Oracle.DatabaseError: ORA-29972: user does not have privilege to change/ create registration

It seems that connection was established succesfully as I am able to read data from my table. cur.execute('select * from mytab') works fine. But when it comes to con.subscribe(), the error above appears.

Do I need to specify any aditional grants?

Or something wrong with how I use the syntax of con.subscribe() ?

os: Linux 6.5

python: 2.6.6

cx_Oracle: 5.2.1

db oracle: 12.1.0.2.0

Here is the code:

import cx_Oracledef DCNCallback(message):     print "Notification:"     for tab in message.tables:         print "Table:", tab.name        for row in tab.rows:             if row.operation & cx_Oracle.OPCODE_INSERT:                 print "INSERT of rowid:", row.rowid            if row.operation & cx_Oracle.OPCODE_DELETE:                 print "DELETE of rowid:", row.rowidhost = 'myhost'port = '1521'dbase = 'mydb'login = 'myuser'password = 'mypassword'dsn = cx_Oracle.makedsn(host, port, dbase)con = cx_Oracle.connect(login, password, dsn, events = True)cur = con.cursor()cur.execute('select * from mytab')for result in cur:    print resultcur.close()subscriptionInsDel = con.subscribe(callback = DCNCallback, operations = cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE, rowids = True)

Thanks in advance!

Christopher Jones-Oracle

Do I need to specify any aditional grants?

Some grants are needed.  See https://docs.oracle.com/database/121/ADFNS/adfns_cqn.htm#ADFNS018

Before you try with cx_Oracle, check you can use CQN in PL/SQL.  See "15.7.10.1 Creating a PL/SQL Notification Handler" in the above doc.

Christopher Jones-Oracle

Do I need to specify any aditional grants?

Some grants are needed.  See https://docs.oracle.com/database/121/ADFNS/adfns_cqn.htm#ADFNS018

Before you try with cx_Oracle, check you can use CQN in PL/SQL.  See "15.7.10.1 Creating a PL/SQL Notification Handler" in the above doc.

Andrey Komrakov

Thanks!

GRANT EXECUTE ON DBMS_CQ_NOTIFICATION  and  GRANT CHANGE NOTIFICATION  were needed.

It works now.

Christopher Jones-Oracle

I'm glad to help.