Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
The DBMS_CHANGE_NOTIFICATION
package is part of the database change notification feature that provides the functionality to create registration on queries designated by a client application and so to 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.
See Also:
Oracle Database Application Developer's Guide - Fundamentals regarding implementing database change notification.This chapter contains the following topics:
Using DBMS_CHANGE_NOTIFICATION
Overview
Security Model
Constants
Operational Notes
Examples
OBJECT Types
The DBMS_CHANGE_NOTIFICATION
package provides PL/SQL based registration interfaces. A client can use this interface to create registrations on queries based on objects of interest and specify a PL/SQL callback handler to receive notifications. When a transaction changes any of the objects associated with the registered queries and COMMIT
s, this invokes the PL/SQL callback specified during registration. The application can define client-specific processing inside the implementation of its PL/SQL callback handler.
The interface lets you define a registration block (using a mechanism similar to a BEGIN-END
block). The recipient of notifications namely the name of the PL/SQL callback handler and a few other registration properties like time-outs can be specified during the BEGIN phase. Any queries executed subsequently (inside the registration block) are considered "interesting queries" and objects referenced by those queries during query execution are registered. The registration is completed by END
ing the registration block.The registration block lets you create new registrations or add objects to existing registrations.
When a registration is created through the PL/SQL interface, a unique registration ID is assigned to the registration by the RDBMS. The client application can use the registration ID to keep track of registrations created by it. When a notification is published by the RDBMS, the registration ID will be part of the notification.
Typical Applications
This functionality is useful for example to applications that cache 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. On changes to objects referenced inside those queries, the database publishes a notification when the underlying transaction commits. In response to the notification, the mid-tier application can refresh its cache by re-executing the query/queries.
The DBMS_CHANGE_NOTIFICATION
package requires that the user have the CHANGE
NOTIFICATION
system privilege in order to receive notifications, and be granted EXECUTE
privilege on the DBMS_CHANGE_NOTIFICATION
package.
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.
The DBMS_CHANGE_NOTIFICATION
package uses the constants shown in Table 23-1. The constants are used as flag parameters either during registration or when received during the notification.
The DBMS_CHANGE_NOTIFCATION
package has sets of constants:
EVENT_STARTUP
, EVENT_SHUTDOWN
, EVENT_SHUTDOWN_ANY
, EVENT_DEREG
describe the type of the notification published by the database.
INSERTOP
, DELETEOP
, UPDATEOP
, ALTEROP
, DROPOP
and UNKNOWNOP
describe the type of operation on a table (during a notification published by the database).
QOS_RELIABLE
, QOS_DEREG_NFY
, QOS_ROWIDs
describe registration Quality of Service properties that the client requires. These are specified during registration.
Table 23-1 DBMS_CHANGE_NOTIFICATION Constants
Name | Type | Value | Description |
---|---|---|---|
ALL_OPERATIONS |
BINARY_INTEGER |
0 |
Interested in being notified on all operations, specified as a parameter during registration |
ALL_ROWS BINARY_INTEGER |
BINARY_INTEGER |
1 |
All rows within the table may have been potentially modified |
EVENT_STARTUP |
BINARY_INTEGER |
1 |
Instance startup notification |
EVENT_SHUTDOWN |
BINARY_INTEGER |
2 |
Instance shutdown notification |
EVENT_SHUTDOWN_ANY |
BINARY_INTEGER |
3 |
Any instance shutdown when running RAC |
EVENT_DEREG |
BINARY_INTEGER |
5 |
Registration has been removed |
EVENT_OBJCHANGE |
BINARY_INTEGER |
6 |
Notification for object change |
INSERTOP |
BINARY_INTEGER |
2 |
Insert operation |
UPDATEOP |
BINARY_INTEGER |
4 |
Update operation |
DELETEOP |
BINARY_INTEGER |
8 |
Delete operation |
ALTEROP |
BINARY_INTEGER |
16 |
Table altered |
DROPOP |
BINARY_INTEGER |
32 |
Table dropped |
UNKNOWNOP |
BINARY_INTEGER |
64 |
Unknown operation |
QOS_RELIABLE |
BINARY_INTEGER |
1 |
Reliable or persistent notification. Also implies that the notifications will be inserted into the persistent storage atomically with the committing transaction that results in an object change. |
QOS_DEREG_NFY |
BINARY_INTEGER |
2 |
Purge registration on first notification |
QOS_ROWIDS |
BINARY_INTEGER |
4 |
Require rowids of modified rows |
The notifications are published by the database when a transaction changes the registered objects and COMMIT
s.
All objects referenced in the queries executed inside the registration block starting from the previous NEW_REG_START
or ENABLE_REG
to REG_END
are considered interesting objects and added to the registration.
Troubleshooting
If you have created a registration and seem to not receive notifications when the underlying tables are changed, please check the following.
Is the job_queue_processes
parameter set to a non-zero value? This parameter needs to be configured to a non-zero 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 COMMIT
ing the transaction? Please note that the notifications are transactional and will be generated when the transaction COMMIT
s.
It maybe possible that there are run-time errors during the execution of the PL/SQL callback due to implementation errors. 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 is 12483, the trace file might be named 'dbs1_j000_12483
.trc
.
Suppose a registration is created with 'chnf_callback
as the notification handler and with registration_id
100. Let us suppose the user forgets to define the chnf_callback
procedure. Then the JOBQ
trace file might contain a message of the following 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
See Also:
For more information about troubleshooting Database Change Notification, see Oracle Database Application Developer's Guide - Fundamentals.Suppose that a mid-tier application has a lot of queries on the HR
.EMPLOYEES
table. If the EMPLOYEES
table is infrequently updated, it can obtain better performance by caching rows from the table because that would avoid a round-trip to the backend database server and server side execution latency. Let us assume that the application has implemented a mid-tier HTTP listener that listens for notifications and updates the mid-tier cache in response to a notification.
The DBMS_CHANGE_NOTIFICATION
package can be utilized in this scenario to send notifications about changes to the table by means of the following steps:
Implement a mid-tier listener component of the cache management system (for example, using HTTP) that listens to notification messages sent from the database and refreshes the mid-tier cache in response to the notification.
Create a server side stored procedure to process notifications
CONNECT / AS SYSDBA; GRANT CHANGE NOTIFICATION TO hr; GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO hr; Rem Enable job queue processes to receive notifications. ALTER SYSTEM SET "job_queue_processes"=2; 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(30)); Rem Create a PL/SQL callback handler to process notifications. 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(20); 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; /
In Step 2 we can send as much information about the invalidation as the mid-tier application needs based on the information obtained from the notification descriptor.
Create a registrations on the tables that we wish to be notified about. We pass in the previously defined procedure name (chnf_callback
) as the name of the server side PL/SQL procedure to be executed when a notification is generated.
Rem Create a REGISTRATION on the EMPLOYEES TABLE DECLARE REGDS SYS.CHNF$_REG_INFO; regid NUMBER; mgr_id NUMBER; dept_id NUMBER; qosflags NUMBER; BEGIN qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS; REGDS := SYS.CHNF$_REG_INFO ('chnf_callback', qosflags, 0,0,0); regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS); SELECT manager_id INTO mgr_id FROM EMPLOYEES WHERE employee_id = 200; DBMS_CHANGE_NOTIFICATION.REG_END; END; /
Once the registration is created in Step 3 above, the server side PL/SQL procedure defined in Step 2 is executed in response to any COMMIT
ted changes to the HR
.EMPLOYEES
table. 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
Notes
In the above example, a registration was created on the EMPLOYEES
table with 'chnf_callback
' as the PL/SQL handler for notifications. During registration, the client specified reliable notifications (QOS_RELIABLE
) and rowid notifications (QOS_ROWIDS
)
The handler accesses the table descriptor array from the notification descriptor only if the notification type is of EVENT_OBJCHANGE
. In all other cases (e.g EVENT_DEREG
, EVENT_SHUTDOWN
), the table descriptor array should not be accessed.
The handler accesses the row descriptor array from the table notification descriptor only if the ALL_ROWS
bit is not set in the table operation flag. If the ALL_ROWS
bit is set in the table operation flag, then it means that all rows within the table may have been potentially modified. In addition to operations like TRUNCATE
that affect all rows in the tables, this bit may also be set if individual rowids have been rolled up into a FULL table invalidation.
This can occur if too many rows were modified on a given table in a single transaction (more than 80) or the total shared memory consumption due to rowids on the RDBMS is determined too large (exceeds 1 % of the dynamic shared pool size). In this case, the recipient must conservatively assume that the entire table has been invalidated and the callback/application must be able to handle this condition.
Also note that the implementation of the user defined callback is up to the developer. In the above example, the callback was used to record event details into database tables. The application can additionally send the notification details to a mid-tier HTTP
listener of its cache management system (as in the example) using UTL_HTTP
. The listener could then refresh its cache by querying from the back-end database.
The DBMS_CHANGE_NOTIFICATION
package uses the following OBJECT
types.
OBJECT Types
This is the top level change notification descriptor type.
Syntax
TYPE SYS.CHNF$_DESC IS OBJECT( registration_id NUMBER, transaction_id RAW(8), dbname VARCHAR2(30), event_type NUMBER, numtables NUMBER, table_desc_array CHNF$_TDESC_ARRAY)
Attributes
Table 23-2 SYS.CHNF$_DESC Object Type
Attribute | Description |
---|---|
registration_id |
Registration ID returned during registration |
transaction_id |
Transaction ID. transaction_id of the transaction that made the change. Will be NULL unless the event_type is EVENT_OBJCHANGE |
dbname |
Name of database |
event_type |
Database event associated with the notification. Can be one of EVENT_OBJCHANGE (change to a registered object), EVENT_STARTUP , EVENT_SHUTDOWN or EVENT_DEREG (registration has been removed due to a timeout or other reason) |
numtables |
Number of modified tables. Will be NULL unless the event_type is EVENT_OBJCHANGE . |
table_desc_array |
Array of table descriptors. Will be NULL unless the event_type is EVENT_OBJCHANGE . |
This object type is the table descriptor that contains an array of row descriptors.
Syntax
TYPE SYS.CHNF$_TDESC IS OBJECT OF ( opflags NUMBER, table_name VARCHAR2(64), numrows NUMBER, row_desc_array CHNF$_RDESC_ARRAY);
Attributes
Table 23-3 TYPE SYS.CHNF$_TDESC Object Type
Attribute | Description |
---|---|
opflags |
Table level operation flags. This is a flag field (bit-vector) which describes the operations that occurred on the table. It can be an OR of the following bit fields - INSERTOP , UPDATEOP , DELETEOP , DROPOP , ALTEROP , ALL_ROWS . If the ALL_ROWS (0x1) bit is set it means that either the entire table is modified (for example, DELETE * FROM t ) or row level granularity of information is not requested or not available in the notification and the receiver has to conservatively assume that the entire table has been invalidated. |
table_name |
Name of modified table |
numrows |
Number of modified rows within the table. numrows will be NULL and hence should not be accessed if the ALL_ROWS bit is set in the table change descriptor. |
row_desc_array |
Array of row descriptors. This field will be NULL if the ALL_ROWS bit is set in opflags . |
This object is the table descriptor type that describes the operations that occurred on a registered table.
Syntax
TYPE SYS.CHNF$_TDESC_ARRAY IS VARRAY (1024) OF CHNF$_TDESC;
This object type is the notification descriptor received as an argument of the server side PL/SQL procedure which contains the details of the invalidation. This object type describes modifications to an individual row within a changed table.
An array of CHNF$_RDESC
is embedded inside a CHNF$_TDESC
(table change descriptor) if the QOS_ROWIDS
option was chosen at the time of registration and the ALL_ROWS
bit is not set in the opflags
field of the table change descriptor.
Syntax
TYPE SYS.CHNF$_RDESC IS OBJECT OF ( opflags NUMBER, row_id VARCHAR2(2000));
Attributes
Table 23-4 TYPE SYS.CHNF$_RDESC Object Type
Attribute | Description |
---|---|
opflags |
Row level operation flags. The flag field (bit vector) describes the operations in the row (could be INSERTOP , UPDATEOP or DELETEOP ). |
row_id |
The rowid of the modified row |
This object type corresponds to an array of row change notification descriptors and is embedded inside the table change descriptor (CHNF$_TDESC
) if QOS_ROWIDS
was specified during registration and the ALL_ROWS
bit is not set in the opflags
field of the table change descriptor.
Syntax
TYPE SYS.CHNF$_RDESC_ARRAY IS VARRAY (1024) OF CHNF$_RDESC;
The object type describes the attributes associated with creating a new registration.
Syntax
TYPE SYS.CHNF$_REG_INFO IS OBJECT ( callback VARCHAR2(20), quosflags NUMBER, timeout NUMBER, operations_filter NUMBER, transaction_lag NUMBER);
Attributes
Table 23-5 TYPE SYS.CHNF$_REG_INFO Object Type
Attribute | Description |
---|---|
callback |
Name of the server side PL/SQL procedure to be executed on a notification. Prototype is <call_backname>(ntfnds IN SYS.chnf$_desc) |
qosflags |
Quality of service flags. Can be set to an OR of QOS_RELIABLE / QOS_DEREG_NFY / QOS_ROWIDS :
|
timeout |
If set to a non-zero value, specifies the time in seconds after which the registration is automatically expunged by the database. If zero / NULL , the registration lives until explicitly deregistered. Note that the timeout option can be combined with the purge on notification (QOS_DEREG_NFY) option as well. |
operations_filter |
if non-zero, specifies a filter to be selectively notified on certain operations. These flags can be used to filter based on specific operation types:
A combination of operations can be specified by using a bitwise |
transaction_lag |
Lag between consecutive notifications in units of transactions. Can be used to specify the number of transactions/database changes, by which the client is willing to lag behind the database. If 0, it means that the client needs to receive an invalidation message as soon as it is generated |
Usage Notes
In response to a database change, the server side PL/SQL procedure specified by "callback" is executed. The PL/SQL procedure name has to be specified in the format s
chema_name.procedure_name
. The procedure must have the following signature:
PROCEDURE <procedure_name>(ntfnds IN SYS.chnf$_desc)
CHNF$_DESC
describes the change notification descriptor.
The init.ora parameter job_queue_processes
must be set to a non-zero value to receive PL/SQL notifications, because the specified procedure is executed inside a job queue process when a notification is generated.
Table 23-6 DBMS_CHANGE_NOTIFICATION Package Subprograms
Subprogram | Description |
---|---|
DEREGISTER Procedure |
De-subscribes the client with the supplied registration identifier (ID) |
ENABLE_REG Procedure |
Begins a registration block using an existing registration identifier (ID) |
NEW_REG_START Function |
Begins a new registration block |
REG_END Procedure |
Ends the registration boundary |
This procedure desubscribes the client with the specified registration identifier (ID).
Syntax
DBMS_CHANGE_NOTIFICATION.DEREGISTER ( regid IN NUMBER);
Parameters
Usage Notes
Only the user that created the registration (or the SYS
user) will be able to desubscribe the registration.
This procedure adds objects to an existing registration identifier (ID). This subprogram is similar to the interface for creating a new registration, except that it takes an existing regid
to which to add objects.Subsequent execution of queries causes the objects referenced in the queries to be added to the specified regid
, and the registration is completed on invoking the REG_END Procedure.
Syntax
DBMS_CHANGE_NOTIFICATION.ENABLE_REG ( regid IN NUMBER);
Parameters
Usage Notes
Only the user that created the registration will be able to add further objects to the registration.
This procedure begins a new registration block. Any objects referenced by queries executed within the registration block are considered interesting objects and added to the registration. The registration block ends upon calling the REG_END
procedure.
Syntax
DBMS_CHANGE_NOTIFICATION.NEW_REG_START ( regds IN sys.chnf$_reg_info) RETURN NUMBER;
Parameters
Table 23-9 NEW_REG_START Function Parameters
Parameter | Description |
---|---|
sys.chnf$_reg_info |
Registration descriptor describing the notification handler and other properties of the registration |
Return Values
The procedure returns a registration-id which is a unique integer assigned by the database to this registration. The registration-id will be echoed back in every notification received for this registration.
Usage Notes
The only operations permitted inside a registration block are queries (the ones the user wishes to register). DML and DDL operations are not permitted.
The registration block is a session property and implicitly terminates upon exiting the session. While the registration block is a session property, the registration itself is a persistent database entity. Once created, the registration survives until explicitly deregistered by the client application or timed-out or removed by the database for some other reason (such as loss of privileges).
The user must have the CHANGE NOTIFICATION
system privilege and SELECT
privileges on any objects to be registered.
The SYS
user will not be permitted to create new registrations.
Nesting of registration block is not permitted.
This procedure marks the end of the registration block. No newly executed queries are tracked.
Syntax
DBMS_CHANGE_NOTIFICATION.REG_END;