Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
|
|
View PDF |
This section contains the following topics:
Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.
During registration, the application specifies a notification handler and associates a set of interesting queries with the notification handler. A notification handler can be either a server side PL/SQL procedure or a client side C callback. Registrations are created on all objects referenced during the execution of the queries. The notification handler is invoked when a transaction subsequently changes any of the registered objects and commits.
Let us assume that the application is interested in being notified about result set changes to a query on the HR.EMPLOYEES
table. The application can register the query on the hr.employees
table with the database using the Change Notification Feature. If a user adds an employee, then the application can receive a database change notification when a new row is added to the table. A new query of hr.employees
returns the changed result set.
When the database issues change notification, it can contain some or all of the following information:
Names of the modified objects. For example, the notification can specify that the hr.employees
table was changed.
The type of change. For example, the message specifies whether the change was caused by an INSERT
, UPDATE
, DELETE
, ALTER TABLE
, or DROP TABLE
.
The ROWID
s of the changed rows and the type of DML that changed them.
Global events such as STARTUP
and SHUTDOWN
(consistent only). In a Real Applications Cluster, the database delivers a notification when the first instance on the database starts or the last instance shuts down.
The notification contains only metadata about the changed rows or objects rather than the changed data itself. For example, the database does not notify the client that a monthly salary increased from 5000 to 6000. To obtain more recent values for the changed objects or rows, the client must query the database based on the information contained in the notification.
Database Change Notification is useful for an application that caches query result sets on mostly read-only objects in the mid-tier to avoid network round trips to the database. Such an application can create a registration on the queries it is interested in caching using the change notification service. On changes to objects referenced inside those queries, the database publishes a change notification when the underlying transaction commits. In response to the notification, the application can refresh its cache by re-executing the queries.
For example, the users of a Web forum application may not need to view new content as soon as it is inserted into the back-end database. Such an application is intrinsically tolerant of slightly out-of-date data, and hence can benefit from caching in the mid-tier. Database change notification is of help in this scenario in keeping the cache updated with the back-end database.
Database Change Notification is relevant in many development contexts, but is particularly useful to mid-tier applications that rely on cached data. Figure 13-1 illustrates a typical scenario in which a back-end Oracle Database serves data that is cached in the middle-tier and then accessed over the Internet.
Applications in the middle tier require rapid access to cached copies of database objects while keeping the cache as current as possible in relation to the database. Cached data becomes out of date or "stale" when a transaction modifies the data and commits, thereby putting the application at risk of accessing incorrect results. If the application uses Database Change Notification, then Oracle Database can publish a notification when a change occurs to registered objects with details on what changed. In response to the notification, the application can refresh cached data by fetching it from the back-end database.
Figure 13-2 illustrates the process by which middle-tier Web clients can receive change and process notifications.
Figure 13-2 Basic Process of Database Change Notification
The explanation of the steps in Figure 13-2 is as follows:
In this example, let's assume that the application has cached the result set of a query on HR.EMPLOYEES
. The developer creates a registration for the query on HR.EMPLOYEES
using the Change Notification PL/SQL Interface. In addition, he creates a stored PL/SQL procedure to process notifications and supplies the server-side PL/SQL procedure as the notification handler.
The database populates the registration information in the data dictionary.
A user modifies one of the registered objects with DML statements and commits the transaction. For example, a user updates a row in the hr.employees
table on the back-end database. The data for hr.employees
cached in the middle tier is now stale.
Oracle Database adds a message that describes the change to an internal queue.
A JOBQ
background process is notified of a new change notification message.
The JOBQ
process executes the stored procedure specified by the client application. In this example, JOBQ
passes the data to a server-side PL/SQL procedure. The implementation of the PL/SQL callback procedure determines how the notification is handled.
Inside the server-side PL/SQL procedure, the developer can implement logic to notify the mid-tier client application of the changes to the registered objects. For example, it notifies the application of the ROWID
of the changed row in hr.employees
.
The client application in the middle tier queries the back-end database to retrieve the data in the changed row.
The client application updates the cache with the new data.
Note:
The above steps are applicable to registrations created through PL/SQL. In the case of registrations created via the OCI interface, the application uses theOCISubscriptionRegister
interface to create a registration and specifies a client side C callback as the notification handler. After registration, an event thread is spawned on the client side program in the process. When a transaction changes any of the registered objects and commits, the EMON
process of the RDBMS sends the notification to the event thread. The C callback specified by the application is then executed in the context of the event thread.This section contains the following topics:
In order to create a registration for change notification, the user is required to have the CHANGE
NOTIFICATION
system privilege. In addition the user is required to have SELECT
privileges on all objects to be registered. Note that if the SELECT
privilege on an object was granted at the time of registration creation but lost subsequently (due to a revoke), then the registration will be purged and a notification to that effect will be published.
A database change registration can be conceptually thought off as a (recipient R, list-of-queries QL) tuple. The recipient is notified when a change occurs to any of the objects referenced in the queries in the Query List. The recipient can be either a server side PL/SQL stored procedure or a client side C callback. Once created a registration is a persistent entity stored in an Oracle database. It is visible to all instances of an Oracle Real Applications Cluster. Transactions that modify registered objects in any instance of the cluster generate notifications. 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 (such as loss of privileges).
If you have been granted the CHANGE NOTIFICATION
privilege in a database session, then you can register a query to receive notifications by performing these steps:
Create the notification recipient for the queries that you want to register. The recipient can be one of the following:
PL/SQL stored procedure, as described in "Creating a PL/SQL Stored Procedure as the Change Notification Recipient"
OCI callback function, as described in Oracle Call Interface Programmer's Guide
Create an query registration for a specified notification recipient, as described in "Registering Queries for Change Notification Through PL/SQL". You can perform this registration by executing SQL queries. After the SQL execution the registration is complete.
Note:
You must be connected as a non-SYS user and should NOT be in the middle of an uncommitted transaction in order to be able to create a registration.The dml_locks
init.ora
parameter must have a nonzero value in order to be able to successfully create registrations and receive notifications.
The default value of dml_locks
is nonzero, therefore this requirement is automatically fulfilled if the application does not configure the dml_locks
parameter explicitly.
Change Notification Registrations are persistent by default and survive until the application explicitly unregisters them.
After the registration has been successfully created, the Oracle Database notifies client applications in response to any changes to objects referred to in the registered queries, when the underlying transaction commits. Notifications are generated as a result of DML operations like INSERT
, UPDATE
, and DELETE
(on transaction commit) and DDL operations like ALTER
and DROP
.
The notification includes information on the names of the objects changed, the Transaction-Id of the transaction that made the change and the TYPE
of operation (INSERT
, UPDATE
or DELETE
).
Note:
If multiple registered objects were modified in a single transaction, then the application will receive one notification for every modified object when the transaction commits.Change Notification allows the application to register most query types including queries executed as part of stored procedures and REF
cursors. When performing a registration, the application is required to be only executing queries, that is, DML or DDL operations are not permitted. In addition, the following types of queries are not supported for registration.
Queries on fixed tables or fixed views.
Queries with dblinks inside them
Queries over materialized views
Oracle Database supports the following options for an object registration:
Purge On Notify option: Unregistering after the first change notification.
Timeout option: Specification of a registration expiration after a time interval.
ROWID
s option: ROWID
s of changed rows are part of the notification ROWID
option.
Reliable Notification option: By default, notifications are generated in shared memory. If this option is chosen, notifications are generated in a persistent database queue. The notifications are enqueued atomically with the transaction that changes a registered object. Since the notifications are persistent in the database, if an instance crashes after generating a notification, they can be delivered when it restarts subsequently OR by a surviving instance of the cluster if running RAC. (Note: there is a trade-off involved here between performance of notifications and reliability. Since there are CPU and I/O costs when generating reliable notifications, it is recommended to choose the default in memory option if better notification performance is desired).
Operations filter: Ability to be notified of PARTICULAR operations (for example notifications only for INSERT
AND UPDATE
).
Transaction Lag: Specification of a count between successive notifications.
If the ROWID
option is chosen, then ROWID
s of changed rows are published as part of the notification. The ROWID
s are published in the external string format. From the ROWID
information in the notification, the application should be subsequently able to retrieve the contents of the changed rows by performing a query of the form "SELECT * from table_name_from_notification where ROWID
= rowid_from_notification". The length of the ROWID
is 18 character bytes in the case of regular heap tables. In the case of Index Organized Tables (IOTs), the length of the ROWID
depends on the size of the primary key and therefore could be larger than 18 bytes.
The ROWID
notifications are hierarchically summarized. If enough memory is not available on the server side to hold ROWID
s, then the notification might be rolled up into a FULL-TABLE-NOTIFICATION
(a special flag in the notification descriptor is reserved for this purpose). When such a notification is received, the application must conservatively assume that the entire table (that is, all rows) may have been modified. ROWID
s are not part of such a notification. ROWID
s may be rolled-up if the total shared memory consumption due to ROWID
s is too large (exceeds 1% of the dynamic shared pool size), OR if too many rows were modified in a single registered object within a transaction (more than 80 approximately) OR if the total length of the logical ROWID
s of modified rows for an IOT is too large (exceeds. 1800 bytes approximately.).
When a table is dropped, a DROP
NOTIFICATION
is published. Any registrations on the dropped table will implicitly remove interest from that object (since it does not exist anymore). If those registrations have interest in other objects as well, then the registrations will continue to exist and DML transactions on those other objects will continue to result in notifications on commit. Even if the dropped table is the only object of interest for a particular registration, we still preserve the registration. The user that created that registration can use the registration to add more objects/queries subsequently.
A registration is based on the version and definition of an object at the time the query was registered. If an object was dropped, registrations on the object will lose interest on the object forever. Subsequently, even if a different object was created with a matching name and in the same schema, then the newly created object is a new object for the purposes of existing Database Change Notification Registrations, that is, any changes to this newly created object (with the matching schema/name) will not result in notifications for those registrations that existed on the prior version of the object.
Registration interfaces are available in both PL/SQL and OCI.
The PL/SQL API enables you to define a registration block. The registration properties including the PL/SQL notification handler are specified during the begin phase of the registration block. Any queries executed inside the registration block are considered interesting queries and the objects referenced in those queries are added to the registration. The registration is completed upon ending the registration block. In PL/SQL, you use the DBMS_CHANGE_NOTIFICATION
package to register to receive change notifications.
The OCI Registration APIs involve the invocation of the function OCISubscriptionRegister
in a namespace called the DBCHANGE
namespace. The registration properties including the client side C notification callback are specified as attributes on the subscription handle. On return from OCISubscriptionRegister
, an end-point registration is successfully created in the database. The application can then associate multiple queries with that registration. This can be done by populating the subscription handle as one of the attributes on the statement handle. Registration of objects occurs during statement execution, that is, as part of the OCIStmtExecute
call if the statement handle has a subscription handle in the DBCHANGE
namespace associated with it.
See Also:
For OCI examples, refer to Oracle Call Interface Programmer's Guide, section "Database Change Notification"You can create a PL/SQL stored procedure that the database server invokes in response to a change to a registered object. The procedure that receives the notification must have the following signature, where schema_name
is the name of the database schema and proc_name
is the name of the stored procedure:
PROCEDURE schema_name.proc_name( ntfnds IN SYS.CHNF$_DESC )
The JOBQ
process passes the CHNF$_DESC
object (notification descriptor), whose attributes describe the details of the change, to the callback procedure. For example, the object contains the transaction ID, the type of change that occurred, the tables that were modified, and so forth. The callback procedure can then send this data to a mid-tier client application for further processing.
Note:
TheJOB_QUEUE_PROCESSES
initialization parameter specifies the maximum number of processes that can be created for the execution of jobs. You must set it to a nonzero value to receive PL/SQL notifications because the specified callback procedure is executed inside a job queue process.See Also:
"Interpreting a Database Change Notification" for an explanation of the SYS.CHNF$_DESC
type
"Creating a PL/SQL Callback Procedure" for an example of PL/SQL callback procedure
You must register the database queries for which you want to receive change notifications. The registration occurs in two steps:
Create a CHNF$_REG_INFO
object that specifies the name of the callback procedure and other metadata concerning the notification.
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.
An object of type CHNF$_REG_INFO
specifies the callback procedure that the database should execute when one of your registered objects changes. You can view the type attributes in SQL*Plus by running the following command:
DESC SYS.CHNF$_REG_INFO
Table 13-1 provides brief descriptions of the attributes of SYS.CHNF$_REG_INFO
.
Table 13-1 Attributes of SYS.CHNF$_REG_INFO
Attribute | Description |
---|---|
|
Specifies the name of the PL/SQL callback procedure to be executed when a notification is generated. You must specify the name in the form |
|
Specifies one of the following constants in the
It is possible to specify a combination of the above options using bitwise OR for example, ( |
|
Specifies the timeout period for registrations. If set to a nonzero value, it specifies the time in seconds after which the database purges the registration. If Note: You can combine the |
|
Filters messages based on types of SQL statement. You can specify the following constants in the
You can specify a combination of operations with a bitwise |
|
Specifies the number of transactions or database changes by which the client can lag behind the database. If Note1: Most applications that need to be notified of changes to an object on transaction commit without further deferral would be expected to chose 0 transaction lag. A non-zero transaction lag is useful only if an application wishes to implement some flow control on notifications. When using nonzero transaction lag, it is recommended that the application workload has the property that notifications are generated at a reasonable periodicity in time. Otherwise, notifications maybe deferred indefinitely till the lag is satisfied. Note2: If you specify |
Suppose that you want to invoke the procedure hr.dcn_callback
whenever a registered object changes. In Example 13-1, you create a CHNF$_REG_INFO
object that specifies that hr.dcn_callback
should receive change notifications. Note that to create the object you must have EXECUTE
privileges on the DBMS_CHANGE_NOTIFICATION
package.
Example 13-1 Creating a CHNF$_REG_INFO Object
DECLARE v_cn_addr SYS.CHNF$_REG_INFO; BEGIN -- create the object v_cn_addr := SYS.CHNF$_REG_INFO ( 'hr.dcn_callback', -- name of PL/SQL callback procedure DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, -- include rowids of modified objects 0, -- registration persists until unregistered 0, -- notify on all types of DML 0 -- notify immediately (no transaction lag) ); -- ... register objects ... END; /
Use the subprograms in the DBMS_CHANGE_NOTIFICATION
package to register queries for a specified notification recipient. For complete documentation for this package, refer to Oracle Database PL/SQL Packages and Types Reference. You can view the package contents in SQL*Plus by connecting as SYS
and running the following command:
DESC SYS.DBMS_CHANGE_NOTIFICATION
Table 13-2 provides brief descriptions of the DBMS_CHANGE_NOTIFICATION
subprograms.
Table 13-2 DBMS_CHANGE_NOTIFICATION Subprograms
Program Unit Signature | Description |
---|---|
|
Marks the beginning of a registration block for inbound object After calling this function, you can execute the queries that you want to register and then end the registration boundary by calling |
|
Marks the end of the registration boundary that you started with |
|
Adds a database object to an existing registration_id. This interface is similar to |
|
Disables the registration specified by its registration ID. |
For an example of an object registration, suppose that the client requires notification whenever a row changes in the hr.employees
table. Example 13-2 shows an anonymous PL/SQL block that registers this table with the hr.dcn_callback
procedure. Note that you must have been granted the CHANGE
NOTIFICATION
privilege to execute this block.
Example 13-2 Registering the Employees Table for Change Notifications
DECLARE v_cn_recip SYS.CHNF$_REG_INFO; v_regid NUMBER; v_employee_id hr.employees.manager_id%TYPE; BEGIN v_cn_recip := SYS.CHNF$_REG_INFO('hr.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 hr.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; /
In Example 13-2, the program registers the object itself, that is, the hr.employees
table. The WHERE
clause restricts the query to the first employee in the result set to avoid generating an error because the fetch returns multiple rows. The DBMS_CHANGE_NOTIFICATION
package registers the object itself, which means that any change to the table—regardless of whether the change is to the row returned by the registered query—generates a notification.
Suppose that later you decide to add a query against the hr.departments
table to the registration ID for the hr.employees
query. After you retrieve the registration ID either from the saved SQL*Plus output or a query of USER_CHANGE_NOTIFICATION_REGS
, you can add this object with the code in Example 13-3 by substituting the numeric ID for reg_id
.
Example 13-3 Adding an Object to an Existing Registration
DECLARE
v_department_id hr.departments.department_id%TYPE;
BEGIN
-- begin registration boundary
DBMS_CHANGE_NOTIFICATION.ENABLE_REG(reg_id);
SELECT department_id
INTO v_department_id
FROM hr.departments
WHERE ROWNUM < 2; -- register this query
-- end registration boundary
DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/
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
For example, you can obtain the registration ID for a client and the list of objects for which it receives notifications. To view registration-ids and table names for HR
, you can do the following from SQL*Plus:
connect hr/hr; SELECT regid, table_name FROM USER_CHANGE_NOTIFICATION_REGS;
See Also:
Oracle Database Reference for descriptions ofDBA_CHANGE_NOTIFICATION_REGS
and USER_CHANGE_NOTIFICATION_REGS
When a transaction commits, Oracle Database determines whether registered objects were modified in the transaction. If the database finds interested clients, it executes the callback procedure specified in the registration.
The details of a database change are exposed through descriptors that the database pass as arguments to a C callback or PL/SQL procedure. Specifically, Oracle Database passes an object of type CHNF$_DESC
, which is the top-level change notification descriptor. You can view the type attributes in SQL*Plus by connecting as SYS
and running the following command:
DESC SYS.CHNF$_DESC
Table 13-3 provides brief descriptions of the attributes of CHNF$_DESC
.
Table 13-3 Attributes of SYS.CHNF$_DESC
Attribute | Specifies . . . |
---|---|
|
The registration ID that was returned during registration. |
|
The ID for the transaction that made the change. |
|
The name of the database in which the changed objects reside. |
|
The database event that triggers a notification. For example, the attribute can contain the following constants, which correspond to different database events:
|
|
The number of tables that were modified. |
|
A |
The CHNF$_DESC
type contains an attribute called TABLE_DESC_ARRAY
, which holds an array of table descriptors of type CHNF$_TDESC
. You can view the type attributes in SQL*Plus by connecting as SYS
and running the following command:
DESC CHNF$_TDESC
Table 13-4 provides brief descriptions of the attributes of CHNF$_TDESC
.
Table 13-4 Attributes of SYS.CHNF$_TDESC
Attribute | Specifies . . . |
---|---|
|
The type of operation performed on the modified table. For example, the attribute can contain the following constants, which correspond to different database operations:
|
|
The name of the modified table. |
|
The number of modified rows. |
|
A |
If the ROWID
option was chosen during registration, the CHNF$_TDESC
type in turn holds an array of type CHNF$_RDESC
, which contains the ROWID
s for the changed rows. Note: if ALL_ROWS
was set in the opflags
field of the CHNF$_TDESC
object, then ROWID
information is not available.
Table 13-5 provides brief descriptions of the attributes of CHNF$_RDESC
.
Table 13-5 Attributes of SYS.CHNF$_RDESC
Attribute | Specifies . . . |
---|---|
|
The type of operation performed on the modified table. See the description of |
|
The |
In this scenario, you are a developer who manages a Web application that provides employee data: name, location, phone number, and so forth. The application, which runs on Oracle Application Server, is heavily used and processes frequent queries of the hr.employees
and hr.departments
tables in the back-end database. Because these tables change relatively infrequently, the application can improve performance by caching table rows. Caching avoids a round trip to the back-end database as well as server-side execution latency.
You can use the DBMS_CHANGE_NOTIFICATION
package to register a query based on hr.employees
and hr.departments
tables. To configure database change notification, you follow these steps:
Implement a mid-tier HTTP listener that listens for notifications and updates the mid-tier cache in response to a notification of a change to the hr.employees
and hr.departments
tables
Create a server-side PL/SQL stored procedure to process the change notifications, as described in "Creating a PL/SQL Callback Procedure"
Register the hr.employees
and hr.departments
tables, as described in "Registering the Query"
After you complete these steps, the server-side PL/SQL procedure defined in step 2 executes in response to changes to hr.employees
or hr.departments
. The callback procedure notifies the Web application of the tables changed. In response to the notification, the application refreshes the cache by querying the back-end database.
In this step, you write a server-side stored procedure to process change notifications. You first connect to the database as a user with DBA
privileges and grant EXECUTE
privileges to hr
:
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO HR; GRANT CHANGE NOTIFICATION TO HR;
Enable the job_queue_processes parameter to receive notifications:
ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;
You may want to create database tables to hold the record of notification events received:
connect hr/hr; 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));
You then create the procedure hr.chnf_callback
, as shown in Example 13-4.
Example 13-4 Server-Side PL/SQL Callback Procedure
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; /
After you have created the callback procedure, you register the query based on the tables for which you want to receive notifications. In Example 13-5, you pass in hr.chnf_callback
as the name of the server-side PL/SQL procedure to be executed when the database generates a notification. Note that you must have the CHANGE NOTIFICATION
privilege to create the procedure.
Example 13-5 Table Registration
CREATE OR REPLACE PROCEDURE hr.table_reg IS v_regds SYS.CHNF$_REG_INFO; v_regid NUMBER; v_employee_id NUMBER; v_department_id NUMBER; BEGIN v_regds := SYS.CHNF$_REG_INFO ('hr.chnf_callback', DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, 0, 0, 0); v_regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START(v_regds); SELECT employee_id INTO v_employee_id FROM hr.employees -- register employees object WHERE ROWNUM < 2; -- return a single row to avoid multiple fetch error SELECT department_id INTO v_department_id FROM hr.departments -- register departments object WHERE ROWNUM < 2; -- return a single row to avoid multiple fetch error DBMS_CHANGE_NOTIFICATION.REG_END; END; / EXEC hr.table_reg
You can view the newly created registration by issuing the following query:
SQL> select regid, table_name from user_change_notification_regs; REGID TABLE_NAME ---------- ------------------------------------------------------------- 16 HR.EMPLOYEES 16 HR.DEPARTMENTS
Once the registration is created as shown above, the server side PL/SQL procedure chnf_callback
, as described above, is executed in response to any committed changes to the HR.EMPLOYEES
or HR.DEPARTMENTS
tables. As an example, let us assume that the following update is performed on the employees table:
UPDATE employees SET salary=salary*1.05 WHERE employee_id=203; COMMIT;
Once the notification is processed, you will find rows which might look like the following in the nfevents
, nftablechanges
, and nfrowchanges
tables:
SQL> select * from nfevents; REGID EVENT_TYPE ---------- ---------- 20045 6 SQL> select * from nftablechanges; REGID TABLE_NAME TABLE_OPERATION ------------------------------------------- 20045 HR.EMPLOYEES 4 SQL> select * from nfrowchanges; REGID TABLE_NAME ROW_ID --------------------------------------------- 20045 HR.EMPLOYEES AAAKB/AABAAAJ8zAAF
For best performance of change notification, the following guidelines are presented.Registered objects are few and mostly read-only and that modifications to those objects are the exception rather than the rule. If the object is extremely volatile, then it will cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large number of notifications, it can slow down OLTP throughput due to the overhead of generating the notifications.It is also a good idea to keep the number of duplicate registrations on any given object low (ideally one) in order to avoid the same notification message being replicated to multiple recipients.
If you have created a registration and seem to not receive notifications or you are unable to create a registration, the following is a list of things to check for.
Is the job_queue_processes
parameter set to a nonzero value? This parameter needs to be configured to a nonzero value in order to receive PL/SQL notifications via the handler.
Are the registrations being created as a NON-SYS user?
If you are attempting DML changes on the registered object, are you committing the transaction? Note that the notifications are transactional and will be generated when the transaction commits.
To check that the registrations on the objects have been successfully created in the database, you can query from the USER_CHANGE_NOTIFICATION_REGS
or DBA_CHANGE_NOTIFICATION_REGS
views. For example, to view all registrations and the registered objects for the current user, you can issue the following select:
SELECT regid, table_name FROM user_change_notification_regs;
It maybe possible that there are run-time errors during the execution of the PL/SQL callback due to implementation errors in the callback. If so, they would be logged to the trace file of the JOBQ
process that attempts to execute the procedure. The trace file would be usually named <ORACLE_SID>_j*_<PID>.trc
. For example, if the ORACLE_SID is 'dbs1' and the process id of the JOBQ
process is 12483, the trace file might be named 'dbs1_j000_12483.trc'.
If there are run-time errors, then it will be reported to the JOBQ
trace file. For example, let's say a registration is created with 'chnf_callback' as the notification handler and registration id 100. Let's say the 'chnf_callback' stored procedure was not DEFINED in the database. Then the JOBQ
trace file might contain a message of the form:
*************************************************************************** Runtime error during execution of PL/SQL cbk chnf_callback for reg CHNF100. Error in PLSQL notification of msgid: Queue : Consumer Name : PLSQL function :chnf_callback Exception Occured, Error msg: ORA-00604: error occurred at recursive SQL level 2 ORA-06550: line 1, column 7: PLS-00201: identifier 'CHNF_CALLBACK' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ****************************************************************************
If you are running into errors during the execution of the callback, consider creating a very simple version of the callback as shown below to verify that you are actually receiving notifications. The callback can be gradually evolved to add more application logic. For example, if the user is HR
then you might consider creating a very simple version of the notification handler as follows:
Rem create a table in the HR schema to hold a count of number of notifications received. Create table nfcount(cnt number); Insert into nfcount values(0); Commit; CREATE OR REPLACE PROCEDURE chnf_callback (ntfnds IN SYS.CHNF$_DESC) IS BEGIN update nfcount set cnt = cnt+1; commit; END; /
The simple procedure created increments the count column of a table and commits. To verify that notifications are being published, you can query from the table nfcount
to see if the cnt
column is indeed going up when a change is made to a registered object and the transaction committed.
There maybe a time lag between the commit of a transaction and the notification received by the end user.