TL;DR

1

GRANT CHANGE NOTIFICATION TO app_user;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

import oracledb

def on_change(message):
    for query in message.queries:
        for table in query.tables:
            for row in table.rows:
                print(table.name, row.operation, row.rowid)

conn = oracledb.connect(user="app_user", password=pw,
                        dsn="db.example:1521/orclpdb1", events=True)

sub = conn.subscribe(callback=on_change,
                     operations=oracledb.OPCODE_INSERT | oracledb.OPCODE_UPDATE | oracledb.OPCODE_DELETE,
                     qos=oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS)
sub.registerquery("select id, status, updated_at from orders")   # explicit columns, never SELECT *

input("Ctrl+C to exit\n")

Every COMMIT that changes the result of that query fires on_change with the affected rowids. No polling, no triggers, no GoldenGate.

Why

When another system must react to changes in Oracle (invalidate a cache, replicate to another database, kick off a process), the usual approach is asking every N seconds with WHERE updated_at > :last. It works until it does not: rows without a date column, deletes that leave no trace, useless load on the database.

Continuous Query Notification flips the flow: you register a query and Oracle calls you when it changes. In query mode (SUBSCR_QOS_QUERY) it notifies only if the result of that query changes; in object mode (without that flag) it notifies on any change to the tables involved. SUBSCR_QOS_ROWIDS includes the rowids in the notification, so the callback can read exactly those rows.

The case: replicating Oracle into PostgreSQL

oracle_fdw lets you query Oracle tables from PostgreSQL as if they were local, and it is tempting to think a trigger on the foreign table will fire when data changes in Oracle. It does not: triggers on a foreign table only fire for writes made from PostgreSQL through it. The oracle_fdw author says it plainly: there is no automatic way to keep a PostgreSQL table in sync with an Oracle table from the PostgreSQL side; Oracle has to trigger it.

With CQN, the small Python service is the missing piece:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

def on_change(message):
    rowids = [row.rowid for q in message.queries for t in q.tables for row in t.rows]
    with conn.cursor() as cur:
        cur.execute("select id, status, updated_at from orders where rowid in (%s)"
                    % ",".join(f"'{r}'" for r in rowids))
        for id_, status, updated_at in cur:
            pg.execute("insert into orders (id, status, updated_at) values (%s, %s, %s) "
                       "on conflict (id) do update set status = excluded.status, updated_at = excluded.updated_at",
                       (id_, status, updated_at))
    pg.commit()

Deletes arrive with row.operation & oracledb.OPCODE_DELETE and a rowid that no longer exists: delete in PostgreSQL by the key you stored against that rowid, or flag the row.

Requirements

  • Permission: GRANT CHANGE NOTIFICATION TO app_user, besides SELECT on the tables.
  • events=True on the connection that subscribes; without it subscribe() fails.
  • Network back-channel. By default the database opens a connection towards your application to deliver notifications: the client machine needs an IP reachable from the Oracle server and an open port (subscribe(..., ip_address=…, port=…) pins which). Behind NAT, inside a container, or with a firewall in between, notifications simply never arrive and nobody tells you.
  • client_initiated=True removes that problem: your app opens the connection. It requires Thick mode (oracledb.init_oracle_client() with Instant Client 19.4 or later) and a 19.4+ database. In Thin mode, python-oracledb’s default, CQN works but only in the classic mode.
  • job_queue_processes greater than zero on the database: notifications are dispatched through the job queue.

Traps

  • ORA-29977: Unsupported column type for query registration in guaranteed mode: in query mode some column types are not allowed (LOBs, among others), and SELECT * drags them in. List the columns you need, or drop SUBSCR_QOS_QUERY and work in object mode.
  • Notifications are not durable: if your service is down during a change, it does not get it. On startup, reconcile first (by updated_at, or in full) and subscribe afterwards. timeout=0 on subscribe keeps the subscription until the connection dies.
  • One subscription per live connection: the events=True connection must stay open; give it keepalives and reconnect with a re-subscribe if it drops.
  • Heavy volume (thousands of commits per second) is not CQN territory: that is where redo-log CDC comes in (GoldenGate, or Debezium with its Oracle connector over LogMiner).
  • SELECT with non-deterministic functions or over complex views may not register in query mode; simplify the query or fall back to object mode.

Reference: Continuous Query Notification in the python-oracledb documentation and the oracle_fdw discussion.