Database Change
Notification is a feature using which Client applications can receive
notifications when the result set of a registered query changes. The
notifications can be published in response to DML or DDL changes on the objects
that are associated with the queries. For example, if the client application
wants to be notified whenever employee
table is modified, the application can receive a database change notification
when a new row is added to the table. The queries on this table are registered
and the application would be notified on changes to this table. Database Change Notification is relevant to the mid-tier
applications that rely on cached data.
1. Database Change Registration
Create the notification recipient for the
queries that have to be registered. The recipient can be can be a PL/SQL stored
procedure or an OCI callback function
Once created, the registration survives until explicitly
unregistered by the client application or timed-out or implicitly removed by
the database for some other reason. A non–sys user has to create the
registration and registrations can not be created during the mid of a
transaction.
The queries that are not supported for registration are
·
Queries on fixed
tables or fixed views.
·
Queries with dblinks
inside them
·
Queries over
materialized views
Registration Properties
Oracle Database
supports the following options for an object registration:
1. Purge On Notify option – This is to unregister after the first change
notification
2. Timeout option - Specification of registration expiration after a time
interval.
3.
ROWIDs option - ROWIDs of changed rows are part of the notification ROWID option.
4. Reliable Notification option: By default, notifications are generated in
shared memory. If this option is chosen, notifications are generated in a
persistent database queue. Since the notifications are persistent in database,
they can recover even after the database crash.
5. Operations filter: Ability to be notified of particular operations like
insert, delete etc.
6. Transaction Lag: Specification of a count between successive notifications
Creating callback procedure
This is a server-side
stored procedure to process change notifications. First connect to the database
as a user with
DBA privileges and grant EXECUTE privileges to CSMGRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO CSM;
GRANT CHANGE NOTIFICATION TO HR;
Enable the job_queue_processes parameter to receive notifications: This parameter
in init.ora would be normally set to non-zero. Other wise the set the parameter
by issuing the command given below.
ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;
You may want to
create database tables to hold the record of notification events received:
connect CSM/CSM;
Rem Create a table to record notification events
CREATE TABLE nfevents(regid number, event_type number);
Rem Create a table to record changes to registered tables
CREATE TABLE nftablechanges(
regid number,
table_name varchar2(100),
table_operation number);
Rem Create a table to record rowids of changed rows.
CREATE TABLE nfrowchanges(
regid number,
table_name varchar2(100),
row_id varchar2(2000));
CREATE OR REPLACE PROCEDURE chnf_callback(ntfnds IN SYS.CHNF$_DESC) IS
regid NUMBER;
tbname VARCHAR2(60);
event_type NUMBER;
numtables NUMBER;
operation_type NUMBER;
numrows NUMBER;
row_id VARCHAR2(2000);
BEGIN
regid := ntfnds.registration_id;
numtables := ntfnds.numtables;
event_type := ntfnds.event_type;
insert into nfevents values(regid, event_type);
IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
FOR i IN 1..numtables LOOP
tbname := ntfnds.table_desc_array(i).table_name;
operation_type := ntfnds.table_desc_array(I). Opflags;
insert into nftablechanges values(regid, tbname, operation_type);
/* Send the table name and operation_type to client side listener
using UTL_HTTP */
/* If interested in the rowids, obtain them as follows */
IF (bitand(operation_type, dbms_change_notification.ALL_ROWS) = 0) THEN
numrows := ntfnds.table_desc_array(i).numrows;
else
numrows :=0; /* ROWID INFO NOT AVAILABLE */
END IF;
/* The body of the loop is not executed when numrows is ZERO */
FOR j IN 1..numrows LOOP
Row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
insert into nfrowchanges values(regid, tbname, Row_id);
/* optionally Send out row_ids to client side listener using
UTL_HTTP */
END LOOP;
END LOOP;
END IF;
commit;
END;
Registering Queries for Change Notification through PL/SQL
1. Create a
CHNF$_REG_INFO object that specifies the name of the callback procedure and other
metadata concerning the notification.
2. Create or update a query
registration by executing a program unit in the
DBMS_CHANGE_NOTIFICATION package and then execute the queries that you want to register. Note that
you must have been granted the CHANGE NOTIFICATION privilege.DECLARE
v_cn_recip SYS.CHNF$_REG_INFO;
v_regid NUMBER;
v_employee_id csm.employees.manager_id%TYPE;
BEGIN
v_cn_recip := SYS.CHNF$_REG_INFO('csm.dcn_callback',
DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, 0, 0, 0);
-- begin the registration boundary
v_regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START(v_cn_recip);
SELECT employee_id INTO v_employee_id
FROM csm.employees -- register the employees object
WHERE ROWNUM < 2; -- write the query so that it returns a single row
-- end the registration boundary
DBMS_CHANGE_NOTIFICATION.REG_END;
DBMS_OUTPUT.PUT_LINE ('the registration id for this query is '||v_regid);
END;
/
You can view the newly created registration by issuing
the queries on user_change_notification_regs. Once the registration is created
, the server side PL/SQL procedure
chnf_callback, as described above,
is executed in response to any committed changes to the CSM.EMPLOYEES tables.
You can query the
following data dictionary views to obtain information about registered clients
of the Database Change Notification feature:
·
DBA_CHANGE_NOTIFICATION_REGS
·
USER_CHANGE_NOTIFICATION_REGS
