Applications that cache data need to know when the data underneath changes. Continuous Query Notification registers the tables a query reads, and after any committed change to them, Oracle calls a PL/SQL procedure with a description of what changed: which table, which operation, and optionally which rows.
Code for This Guide
The main examples are in the examples/pkg-messaging folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
v_reg := cq_notification$_reg_info('HANDLER_PROCEDURE', qos_flags, timeout,
operations_filter, transaction_lag);
v_id := dbms_cq_notification.new_reg_start(v_reg);
-- run the queries whose tables should be watched
dbms_cq_notification.reg_end;
dbms_cq_notification.deregister(v_id);The handler takes one parameter of type CQ_NOTIFICATION$_DESCRIPTOR. QOS_ROWIDS adds the ROWIDs of changed rows. Registering needs the CHANGE NOTIFICATION privilege.
Watch a Table
NOTE_FARE_CHANGE writes each notified change into a table. The registration watches AIRCRAFT_TYPES, read by the query between NEW_REG_START and REG_END.
Setup:
create table fare_changes (noted_at timestamp default systimestamp,
table_name varchar2(100), operation varchar2(20),
row_id varchar2(30));Example:
create or replace procedure note_fare_change (ntfnds in cq_notification$_descriptor) is
begin
for t in 1 .. ntfnds.table_desc_array.count loop
for r in 1 .. ntfnds.table_desc_array(t).numrows loop
insert into fare_changes (table_name, operation, row_id)
values (ntfnds.table_desc_array(t).table_name,
case when bitand(ntfnds.table_desc_array(t).row_desc_array(r).opflags,
dbms_cq_notification.updateop) > 0 then 'UPDATE' end,
ntfnds.table_desc_array(t).row_desc_array(r).row_id);
end loop;
end loop;
commit;
end;
/
declare
v_reg cq_notification$_reg_info;
v_id number;
v_dummy number;
begin
v_reg := cq_notification$_reg_info('NOTE_FARE_CHANGE',
dbms_cq_notification.qos_rowids, 0, 0, 0);
v_id := dbms_cq_notification.new_reg_start(v_reg);
select count(*) into v_dummy from aircraft_types; -- the tables to watch
dbms_cq_notification.reg_end;
dbms_output.put_line('registration ' || case when v_id > 0 then 'created' end);
end;
/
update aircraft_types set range_km = range_km where type_code = 'A20N';
commit;
exec dbms_session.sleep(5)
select table_name, operation from fare_changes;
select table_name from user_change_notification_regs;Output:
Procedure NOTE_FARE_CHANGE compiled registration created PL/SQL procedure successfully completed. 1 row updated. Commit complete. PL/SQL procedure successfully completed. TABLE_NAME OPERATION ________________________ ____________ NIMBUS.AIRCRAFT_TYPES UPDATE TABLE_NAME ________________________ NIMBUS.AIRCRAFT_TYPES
After an update to one aircraft type commits, the notification arrives in the background within a few seconds, and the handler records an UPDATE on NIMBUS.AIRCRAFT_TYPES. USER_CHANGE_NOTIFICATION_REGS lists the registration.
Things to Know
- Notifications run in a background job session after commit, so the changing session never waits for them.
- With query result change notification (QOS_QUERY), Oracle notifies only when the query's result changes, not on any change to its tables.
- Deregister when done, or notifications keep arriving.
Cleanup:
begin
for r in (select regid from user_change_notification_regs) loop
dbms_cq_notification.deregister(r.regid);
end loop;
end;
/
drop procedure note_fare_change;
drop table fare_changes purge;Related Guides
Conclusion
DBMS_CQ_NOTIFICATION calls your procedure after committed changes to the tables of registered queries, with the table, operation, and rows. Use it to refresh caches when data changes, and deregister when you no longer need it.
