|Oracle® Database Application Developer's Guide - Fundamentals
10g Release 2 (10.2)
|PDF · Mobi · ePub|
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
ALTER TABLE, or
ROWIDs of the changed rows and the type of DML that changed them.
Global events such as
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.
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.
JOBQ background process is notified of a new change notification message.
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
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 the
OCISubscriptionRegisterinterface 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
EMONprocess 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
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.
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
DELETE (on transaction commit) and DDL operations like
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 (
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.
ROWIDs of changed rows are part of the notification
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
Transaction Lag: Specification of a count between successive notifications.
ROWID option is chosen, then
ROWIDs of changed rows are published as part of the notification. The
ROWIDs 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.
ROWID notifications are hierarchically summarized. If enough memory is not available on the server side to hold
ROWIDs, 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.
ROWIDs are not part of such a notification.
ROWIDs may be rolled-up if the total shared memory consumption due to
ROWIDs 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
ROWIDs of modified rows for an IOT is too large (exceeds. 1800 bytes approximately.).
When a table is dropped, a
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.
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 )
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.
JOB_QUEUE_PROCESSESinitialization 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.
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:
Table 13-1 provides brief descriptions of the attributes of
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
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:
Table 13-2 provides brief descriptions of the
|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
NOTIFICATION privilege to execute this block.
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
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; /
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 of
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:
Table 13-3 provides brief descriptions of the attributes of
|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.
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:
Table 13-4 provides brief descriptions of the attributes of
|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.
ROWID option was chosen during registration, the
CHNF$_TDESC type in turn holds an array of type
CHNF$_RDESC, which contains the
ROWIDs 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
|Attribute||Specifies . . .|
The type of operation performed on the modified table. See the description of
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.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.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
Create a server-side PL/SQL stored procedure to process the change notifications, as described in "Creating a PL/SQL Callback Procedure"
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.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
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.
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.
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.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
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.
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
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.