12 Using Continuous Query Notification

Continuous Query Notification (CQN) allows an application to register queries with the database for either object change notification (the default) or query result change notification. An object referenced by a registered query is a registered object.

If a query is registered for object change notification (OCN), the database notifies the application whenever a transaction changes an object that the query references and commits, whether or not the query result changed.

If a query is registered for query result change notification (QRCN), the database notifies the application whenever a transaction changes the result of the query and commits.

A CQN registration associates a list of one or more queries with a notification type (OCN or QRCN) and a notification handler. To create a CQN registration, you can use either the PL/SQL interface or the OCI interface. If you use the PL/SQL interface, the notification handler is a server-side PL/SQL stored procedure; if you use the OCI interface, the notification handler is a client-side C callback procedure.

This chapter explains general CQN concepts and explains how to use the PL/SQL CQN interface. For information about using OCI for CQN, see Oracle Call Interface Programmer's Guide.

Topics:

Note:

The terms OCN and QRCN refer to both the notification type and the notification itself: An application registers a query for OCN, and the database sends the application an OCN; an application registers a query for QRCN, and the database sends the application a QRCN.

Object Change Notification (OCN)

If an application registers a query for object change notification (OCN), the database sends the application an OCN whenever a transaction changes an object associated with the query and commits, whether or not the result of the query changed.

For example, if an application registers the query in Example 12-1 for OCN, and a user commits a transaction that changes the EMPLOYEES table, the database sends the application an OCN, even if the changed row or rows did not satisfy the query predicate (for example, if DEPARTMENT_ID = 5).

Example 12-1 Query to be Registered for Change Notification

SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 10;

Query Result Change Notification (QRCN)

Note:

For QRCN support, the COMPATIBLE initialization parameter of the database must be at least 11.0.0, and Automatic Undo Management (AUM) must be enabled (as it is by default).

For information about the COMPATIBLE initialization parameter, see Oracle Database Administrator's Guide.

For information about AUM, see Oracle Database Administrator's Guide.

If an application registers a query for query result change notification (QRCN), the database sends the application a QRCN whenever a transaction changes the result of the query and commits.

For example, if an application registers the query in Example 12-1 for QRCN, the database sends the application a QRCN only if the query result set changes; that is, if one of the following DML statements commits:

  • An INSERT or DELETE of a row that satisfies the query predicate (DEPARTMENT_ID = 10).

  • An UPDATE to the EMPLOYEE_ID or SALARY column of a row that already satisfied the query predicate (DEPARTMENT_ID = 10).

  • An UPDATE to the DEPARTMENT_ID column of a row that changed its value from 10 to a value other than 10, causing the row to be deleted from the result set.

  • An UPDATE to the DEPARTMENT_ID column of a row that changed its value to 10 from a value other than 10, causing the row to be added to the result set.

The default notification type is OCN. For QRCN, specify QOS_QUERY in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

With QRCN, you have a choice of guaranteed mode (the default) or best-effort mode.

Topics:

Guaranteed Mode

In guaranteed mode, there are no false positives: the database sends the application a QRCN only when the query result set is guaranteed to have changed.

For example, suppose that an application registered the query in Example 12-1 for QRCN, that employee 201 is in department 10, and that the following statements are executed:

UPDATE EMPLOYEES
  SET SALARY = SALARY + 10
    WHERE EMPLOYEE_ID = 201;

UPDATE EMPLOYEES
  SET SALARY = SALARY - 10
    WHERE EMPLOYEE_ID = 201;

COMMIT;

Each UPDATE statement in the preceding transaction changes the query result set, but together they have no effect on the query result set; therefore, the database does not send the application a QRCN for the transaction.

For guaranteed mode, specify QOS_QUERY, but not QOS_BEST_EFFORT, in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

Some queries are too complex for QRCN in guaranteed mode. For the characteristics of queries that can be registered in guaranteed mode, see Queries that Can Be Registered for QRCN in Guaranteed Mode.

Best-Effort Mode

Some queries that are too complex for guaranteed mode can be registered for QRCN in best-effort mode, in which CQN creates and registers simpler versions of them.

For example, the query in Example 12-2 is too complex for QRCN in guaranteed mode because it contains the aggregate function SUM.

Example 12-2 Query Too Complex for QRCN in Guaranteed Mode

SELECT SUM(SALARY)
  FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 20;

In best-effort mode, CQN registers the following simpler version of the query in Example 12-2:

SELECT SALARY
  FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 20;

Whenever the result of the original query changes, the result of its simpler version also changes; therefore, no notifications are lost due to the simplification. However, the simplification might cause false positives, because the result of the simpler version can change when the result of the original query does not.

In best-effort mode, the database does the following:

  • Minimizes the OLTP response overhead that is due to notification-related processing, as follows:

    • For a single-table query, the database determines whether the query result has changed by which columns changed and which predicates the changed rows satisfied.

    • For a multiple-table query (a join), the database uses the primary-key/foreign-key constraint relationships between the tables to determine whether the query result has changed.

  • Sends the application a QRCN whenever a DML statement changes the query result set, even if a subsequent DML statement nullifies the change made by the first DML statement.

As a result of its overhead minimization, best-effort mode infrequently causes false positives, even for queries that CQN does not simplify. For example, consider the query in Example 12-1 and the transaction in Guaranteed Mode. In best-effort mode, CQN does not simplify the query, but the transaction generates a false positive.

Some types of queries are so simplified that invalidations are generated at object level; that is, whenever any object referenced in those queries changes. Examples of such queries are those that use unsupported column types or include subqueries. The solution to this problem is to rewrite the original queries.

For example, the query in Example 12-3 is too complex for QRCN in guaranteed mode because it includes a subquery.

Example 12-3 Query Whose Simplified Version Invalidates Objects

SELECT SALARY FROM EMPLOYEES
  WHERE DEPARTMENT_ID IN
    (SELECT DEPARTMENT_ID FROM DEPARTMENTS
       WHERE LOCATION_ID = 1700
    );

In best-effort mode, CQN simplifies the query in Example 12-3 to this:

SELECT * FROM EMPLOYEES, DEPARTMENTS;

The simplified query can cause objects to be invalidated. However, if you rewrite the original query as follows, you can register it in either guaranteed mode or best-effort mode:

SELECT SALARY FROM EMPLOYEES, DEPARTMENTS
  WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
    AND DEPARTMENTS.LOCATION_ID = 1700;

Queries that can be registered only in best-effort mode are described in Queries that Can Be Registered for QRCN Only in Best-Effort Mode.

The default for QRCN mode is guaranteed mode. For best-effort mode, specify QOS_BEST_EFFORT in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

Events that Generate Notifications

The following events generate notifications:

Committed DML Transactions

When the notification type is OCN, any DML transaction that changes one or more registered objects generates one notification for each object when it commits.

When the notification type is QRCN, any DML transaction that changes the result of one or more registered queries generates a notification when it commits. The notification includes the query IDs of the queries whose results changed.

For either notification type, the notification includes:

  • Name of each changed table

  • Operation type (INSERT, UPDATE, or DELETE)

  • ROWID of each changed row, if the registration was created with the ROWID option and the number of modified rows was not too large. For more information, see ROWID Option.

Committed DDL Statements

For both OCN and QRCN, the following DDL statements, when committed, generate notifications:

  • ALTER TABLE

  • TRUNCATE TABLE

  • FLASHBACK TABLE

  • DROP TABLE

Note:

When the notification type is OCN, a committed DROP TABLE statement generates a DROP NOTIFICATION.

Any OCN registrations of queries on the dropped table become disassociated from that table (which no longer exists), but the registrations themselves continue to exist. If any of these registrations are associated with objects other than the dropped table, committed changes to those other objects continue to generate notifications. Registrations associated only with the dropped table also continue to exist, and their creator can add queries (and their referenced objects) to them.

An OCN registration is based on the version and definition of an object at the time the query was registered. If an object is dropped, registrations on that object are disassociated from it forever. If a new object is created with the same name, and in the same schema, as the dropped object, the new object is not associated with OCN registrations that were associated with the dropped object.

When the notification type is QRCN:

  • The notification includes the following:

    • Query IDs of the queries whose results have changed

    • Name of the modified table

    • Type of DDL operation

  • Some DDL operations that invalidate registered queries can cause those queries to be deregisted.

    For example, suppose that the following query is registered for QRCN:

    SELECT COL1 FROM TEST_TABLE
      WHERE COL2 = 1;
    

    Suppose that TEST_TABLE has the following schema:

    (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER)
    

    The following DDL statement, when committed, invalidates the query and causes it to be removed from the registration:

    ALTER TABLE DROP COLUMN COL2;
    

Deregistration

For both OCN and QRCN, deregistration—removal of a registration from the database—generates a notification. The reasons that the database removes a registration are:

  • Timeout

    If TIMEOUT is specified with a nonzero value when the queries are registered, the database purges the registration after the specified time interval.

    If QOS_DEREG_NFY is specified when the queries are registered, the database purges the registration after it generates its first notification.

  • Loss of privileges

    If privileges are lost on an object associated with a registered query, and the notification type is OCN, the database purges the registration. (When the notification type is QRCN, the database removes that query from the registration, but does not purge the registration.)

    For privileges needed to register queries, see Prerequisites for Creating CQN Registrations.

A notification is not generated when a client application performs an explicit deregistration.

Global Events

The global events EVENT_STARTUP and EVENT_SHUTDOWN generate notifications.

In an Oracle RAC environment, the following events generate notifications:

  • EVENT_STARTUP when the first instance of the database starts up

  • EVENT_SHUTDOWN when the last instance of the database shuts down

  • EVENT_SHUTDOWN_ANY when any instance of the database shuts down

The preceding global events are constants defined in the DBMS_CQ_NOTIFICATION package.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_CQ_NOTIFICATION package

Notification Contents

A notification contains some or all of the following information:

  • Type of event, which is one of the following:

    • Startup

    • Object change

    • Query result change

    • Deregistration

    • Shutdown

  • Registration ID of affected registration

  • Names of changed objects

  • If ROWID option was specified, ROWIDs of changed rows

  • If the notification type is QRCN: Query IDs of queries whose results changed

  • If notification resulted from a DML or DDL statement:

    • Array of names of modified tables

    • Operation type (for example, INSERT or UPDATE)

A notification does not contain the changed data itself. For example, the notification does not say that a monthly salary increased from 5000 to 6000. To obtain more recent values for the changed objects or rows or query results, the application must query the database.

Good Candidates for CQN

Good candidates for CQN are applications that cache the result sets of queries on infrequently changed objects in the middle tier, to avoid network round trips to the database. These applications can use CQN to register the queries to be cached. When such an application receives a notification, it can refresh its cache by reexecuting the registered queries.

An example of such an application is a web forum. Because its users need not view new content as soon as it is inserted into the database, this application can cache information in the middle tier and have CQN tell it when it when to refresh the cache.

Figure 12-1 illustrates a typical scenario in which the database serves data that is cached in the middle tier and then accessed over the Internet.

Figure 12-1 Middle-Tier Caching

Middle-Tier Caching
Description of "Figure 12-1 Middle-Tier Caching"

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 obsolete when a transaction modifies the data and commits, thereby putting the application at risk of accessing incorrect results. If the application uses CQN, the 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 12-2 illustrates the process by which middle-tier Web clients receive and process notifications.

Figure 12-2 Basic Process of Continuous Query Notification (CQN)

Basic Process of Continuous Query Notification (CQN)
Description of "Figure 12-2 Basic Process of Continuous Query Notification (CQN)"

Explanation of steps in Figure 12-2 (assuming that registrations are created using PL/SQL and that the application has cached the result set of a query on HR.EMPLOYEES):

  1. The developer uses PL/SQL to create a CQN registration for the query, which consists of creating a stored PL/SQL procedure to process notifications and then using the PL/SQL CQN interface to create a registration for the query, specifying the PL/SQL procedure as the notification handler.

  2. The database populates the registration information in the data dictionary.

  3. A user updates a row in the HR.EMPLOYEES table in the back-end database and commits the update, causing the query result to change. The data for HR.EMPLOYEES cached in the middle tier is now outdated.

  4. The database adds a message that describes the change to an internal queue.

  5. The database notifies a JOBQ background process of a new notification message.

  6. 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 notification handler determines how the notification is handled.

  7. Inside the server-side PL/SQL procedure, the developer can implement logic to notify the middle-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.

  8. The client application in the middle tier queries the back-end database to retrieve the data in the changed row.

  9. The client application updates the cache with the new data.

Creating CQN Registrations

A CQN registration associates a list of one or more queries with a notification type and a notification handler.

The notification type is either OCN or QRCN. For information about these types, see Object Change Notification (OCN) and Query Result Change Notification (QRCN).

To create a CQN registration, you can use either the PL/SQL interface or the OCI interface. If you use the PL/SQL interface, the notification handler is a server-side PL/SQL stored procedure; if you use the OCI interface, the notification handler is a client-side C callback procedure. (This topic explains only the PL/SQL interface. For information about the OCI interface, see Oracle Call Interface Programmer's Guide.)

Once created, a registration is stored in the database. In an Oracle RAC environment, it is visible to all database instances. Transactions that change the query results in any database instance generate notifications.

By default, a registration survives until the application that created it explicitly deregisters it or until the database implicitly purges it (due to loss of privileges, for example).

Topics:

PL/SQL CQN Registration Interface

The PL/SQL CQN registration interface is implemented with the DBMS_CQ_NOTIFICATION package. You use the DBMS_CQ_NOTIFICATION.NEW_REG_START function to open a registration block. You specify the registration details, including the notification type and notification handler, as part of the CQ_NOTIFICATION$_REG_INFO object, which is passed as an argument to the NEW_REG_START procedure. Every query that you execute while the registration block is open is registered with CQN. If you specified notification type QRCN, the database assigns a query ID to each query. You can retrieve these query IDs with the DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID function. To close the registration block, you use the DBMS_CQ_NOTIFICATION.REG_END function.

For step-by-step instructions, see Using PL/SQL to Register Queries for CQN.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_CQ_NOTIFICATION package

CQN Registration Options

You can change the CQN registration defaults with the options summarized in Table 12-1.

Table 12-1 Continuous Query Notification Registration Options

Option Description

Notification Type

Specifies QRCN (the default is OCN).

QRCN ModeFoot 1 

Specifies best-effort mode (the default is guaranteed mode).

ROWID

Includes the ROWID of each changed row in the notification.

Operations FilterFoot 2 

Publishes the notification only if the operation type matches the specified filter condition.

Transaction LagFootref 2

Deprecated. Use Notification Grouping instead.

Notification Grouping

Specifies how notifications are grouped.

Reliable

Stores notifications in a persistent database queue (instead of in shared memory, the default).

Purge on Notify

Purges the registration after the first notification.

Timeout

Purges the registration after a specified time interval.


Footnote 1 Applies only when notification type is QRCN.

Footnote 2 Applies only when notification type is OCN.

Topics:

Notification Type Option

The notification types are OCN (described in Object Change Notification (OCN)) and QRCN (described in Query Result Change Notification (QRCN)).

QRCN Mode (QRCN Notification Type Only)

The QRCN mode option applies only when the notification type is QRCN. Instructions for setting the notification type to QRCN are in Notification Type Option.

The QRCN modes are guaranteed (described in Guaranteed Mode) and best-effort (described in Best-Effort Mode).

The default is guaranteed mode. For best-effort mode, specify QOS_BEST_EFFORT in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

ROWID Option

To include the ROWID option of each changed row in the notification, specify QOS_ROWIDS in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

From the ROWID information in the notification, the application can retrieve the contents of the changed rows by performing queries of the following form:

SELECT * FROM table_name_from_notification
  WHERE ROWID = rowid_from_notification;

ROWIDs are published in the external string format. For a regular heap table, the length of a ROWID is 18 character bytes. For an Index Organized Table (IOT), the length of the ROWID depends on the size of the primary key, and might exceed 18 bytes.

If the server does not have enough memory for the ROWIDs, the notification might be "rolled up" into a FULL-TABLE-NOTIFICATION, indicated by a special flag in the notification descriptor. Possible reasons for a FULL-TABLE-NOTIFICATION are:

  • Total shared memory consumption due to ROWIDs exceeds 1% of the dynamic shared pool size.

  • Too many rows were changed in a single registered object within a transaction (the upper limit is approximately 80).

  • Total length of the logical ROWIDs of modified rows for an IOT is too large (the upper limit is approximately 1800 bytes).

  • You specified the Notification Grouping option NTFN_GROUPING_TYPE with the value DBMS_CQ_NOTIFICATION.NTFN_GROUPING_TYPE_SUMMARY, described in Notification Grouping Options.

Because a FULL-TABLE-NOTIFICATION does not include ROWIDs, the application that receives it must assume that the entire table (that is, all rows) might have changed.

Operations Filter Option (OCN Notification Type Only)

The Operations Filter option applies only when the notification type is OCN.

The Operations Filter option enables you to specify the types of operations that generate notifications.

The default is all operations. To specify that only some operations generate notifications, use the OPERATIONS_FILTER attribute of the CQ_NOTIFICATION$_REG_INFO object. With the OPERATIONS_FILTER attribute, specify the type of operation with the constant that represents it, which is defined in the DBMS_CQ_NOTIFICATIONS package, as follows:

Operation Constant
INSERT DBMS_CQ_NOTIFICATIONS.INSERTOP
UPDATE DBMS_CQ_NOTIFICATIONS.UPDATEOP
DELETE DBMS_CQ_NOTIFICATIONS.DELETEOP
ALTEROP DBMS_CQ_NOTIFICATIONS.ALTEROP
DROPOP DBMS_CQ_NOTIFICATIONS.DROPOP
UNKNOWNOP DBMS_CQ_NOTIFICATIONS.UNKNOWNOP
All (default) DBMS_CQ_NOTIFICATIONS.ALL_OPERATIONS

To specify multiple operations, use bitwise OR. For example:

DBMS_CQ_NOTIFICATIONS.INSERTOP + DBMS_CQ_NOTIFICATIONS.DELETEOP

OPERATIONS_FILTER has no effect if you also specify QOS_QUERY in the QOSFLAGS attribute, because QOS_QUERY specifies notification type QRCN.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_CQ_NOTIFICATION package

Transaction Lag Option (OCN Notification Type Only)

The Transaction Lag option applies only when the notification type is OCN.

Note:

This option is deprecated. To implement flow-of-control notifications, use Notification Grouping Options.

The Transaction Lag option specifies the number of transactions by which the client application can lag behind the database. If the number is 0, every transaction that changes a registered object results in a notification. If the number is 5, every fifth transaction that changes a registered object results in a notification. The database tracks intervening changes at object granularity and includes them in the notification, so that the client does not lose them.

A transaction lag greater than 0 is useful only if an application implements flow-of-control notifications. Ensure that the application generates notifications frequently enough to satisfy the lag, so that they are not deferred indefinitely.

If you specify TRANSACTION_LAG, then notifications do not include ROWIDs, even if you also specified QOS_ROWIDS.

Notification Grouping Options

By default, notifications are generated immediately after the event that causes them.

Notification Grouping options, which are attributes of the CQ_NOTIFICATION$_REG_INFO object, are the following:

Attribute Description
NTFN_GROUPING_CLASS Specifies the class by which to group notifications. Currently, the only allowed values are DBMS_CQ_NOTIFICATION.NTFN_GROUPING_CLASS_TIME, which groups notifications by time, and zero, which is the default (notifications are generated immediately after the event that causes them).
NTFN_GROUPING_VALUE Specifies the time interval that defines the group, in seconds. For example, if this value is 900, notifications generated in the same 15-minute interval are grouped together.
NTFN_GROUPING_TYPE Specifies the type of grouping, which is either of the following:
  • DBMS_CQ_NOTIFICATION.NTFN_GROUPING_TYPE_SUMMARY: All notifications in the group are summarized into a single notification.

    Note: The single notification does not include ROWIDs, even if you specified the ROWID option.

  • DBMS_CQ_NOTIFICATION.NTFN_GROUPING_TYPE_LAST: Only the last notification in the group is published and the earlier ones discarded.

NTFN_GROUPING_START_TIME Specifies when to start generating notifications. If specified as NULL, it defaults to the current system-generated time.
NTFN_GROUPING_REPEAT_COUNT Specifies how many times to repeat the notification. Set to DBMS_CQ_NOTIFICATION.NTFN_GROUPING_FOREVER to receive notifications for the life of the registration. To receive at most n notifications during the life of the registration, set to n.

Note:

Notifications generated by timeouts, loss of privileges, and global events might be published before the specified grouping interval expires. If they are, any pending grouped notifications are also published before the interval expires.

Reliable Option

By default, a CQN registration is stored in shared memory. To store it in a persistent database queue instead—that is, to generate reliable notifications—specify QOS_RELIABLE in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

The advantage of reliable notifications is that if the database fails after generating them, it can still deliver them after it restarts. In an Oracle RAC environment, a surviving database instance can deliver them.

The disadvantage of reliable notifications is that they have higher CPU and I/O costs than default notifications do.

Purge-on-Notify and Timeout Options

By default, a CQN registration survives until the application that created it explicitly unregisters it or until the database implicitly purges it (due to loss of privileges, for example).

To purge the registration after it generates its first notification, specify QOS_DEREG_NFY in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object.

To purge the registration after n seconds, specify n in the TIMEOUT attribute of the CQ_NOTIFICATION$_REG_INFO object.

You can use the Purge-on-Notify and Timeout options together.

Prerequisites for Creating CQN Registrations

The following are prerequistes for creating CQN registrations:

  • You must have the following privileges:

    • EXECUTE privilege on the DBMS_CQ_NOTIFICATION package, whose subprograms you use to create a registration

    • CHANGE NOTIFICATION system privilege

    • SELECT privileges on all objects to be registered

    Loss of privileges on an object associated with a registered query generates a notification—see Deregistration.

  • You must be connected as a non-SYS user.

  • You must not be in the middle of an uncommitted transaction.

  • The dml_locks init.ora parameter must have a nonzero value (as its default value does).

    (This is also a prerequisite for receiving notifications.)

Note:

For QRCN support, the COMPATIBLE setting of the database must be at least 11.0.0.

Queries that Can Be Registered for Object Change Notification (OCN)

Most queries can be registered for OCN, including those executed as part of stored procedures and REF cursors.

Queries that cannot be registered for OCN are the following:

  • Queries on fixed tables or fixed views

  • Queries on user views

  • Queries that contain database links (dblinks)

  • Queries over materialized views

Note:

You can use synonyms in OCN registrations, but not in QRCN registrations.

Queries that Can Be Registered for Query Result Change Notification (QRCN)

Some queries can be registered for QRCN in guaranteed mode, some can be registered for QRCN only in best-effort mode, and some cannot be registered for QRCN in either mode. (For information about modes, see Guaranteed Mode and Best-Effort Mode.)

Topics:

Queries that Can Be Registered for QRCN in Guaranteed Mode

To be registered for QRCN in guaranteed mode, a query must conform to the following rules:

  • Every column that it references is either a NUMBER data type or a VARCHAR2 data type.

  • Arithmetic operators in column expressions are limited to the following binary operators, and their operands are columns with numeric data types:

    • + (addition)

    • - (subtraction, not unary minus)

    • * (multiplication)

    • / (division)

  • Comparison operators in the predicate are limited to the following:

    • < (less than)

    • <= (less than or equal to)

    • = (equal to)

    • >= (greater than or equal to)

    • > (greater than)

    • <> or != (not equal to)

    • IS NULL

    • IS NOT NULL

  • Boolean operators in the predicate are limited to AND, OR, and NOT.

  • The query contains no aggregate functions (such as SUM, COUNT, AVERAGE, MIN, and MAX).

    For a list of built-in SQL aggregate functions, see Oracle Database SQL Language Reference.

Guaranteed mode supports most queries on single tables and some inner equijoins, such as:

SELECT SALARY FROM EMPLOYEES, DEPARTMENTS
  WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
    AND DEPARTMENTS.LOCATION_ID = 1700;

Notes:

  • Sometimes the query optimizer uses an execution plan that makes a query incompatible for guaranteed mode (for example, OR-expansion). For information about the query optimizer, see Oracle Database Performance Tuning Guide.

  • Queries that can be registered in guaranteed mode can also be registered in best-effort mode, but results might differ, because best-effort mode can cause false positives even for queries that CQN does not simplify. For details, see Best-Effort Mode.

Queries that Can Be Registered for QRCN Only in Best-Effort Mode

A query that does any of the following can be registered for QRCN only in best-effort mode, and its simplified version will generated notifications at object granularity:

  • Refers to columns that have encryption enabled

  • Has more than 10 items of the same type in the SELECT list

  • Has expressions that include any of the following:

    • String functions (such as SUBSTR, LTRIM, and RTRIM)

    • Arithmetic functions (such as TRUNC, ABS, and SQRT)

      For a list of built-in SQL functions, see Oracle Database SQL Language Reference.

    • Pattern-matching conditions LIKE and REGEXP_LIKE

    • EXISTS or NOT EXISTS condition

  • Has disjunctions involving predicates defined on columns from different tables. For example:

    SELECT EMPLOYEE_ID, DEPARTMENT_ID
      FROM EMPLOYEES, DEPARTMENTS
        WHERE EMPLOYEES.EMPLOYEE_ID = 10
          OR DEPARTMENTS.DEPARTMENT_ID = 'IT';
    
  • Has user rowid access. For example:

    SELECT DEPARTMENT_ID
      FROM DEPARTMENTS
        WHERE ROWID = 'AAANkdAABAAALinAAF';
    
  • Has any join other than an inner join

  • Has an execution plan that involves any of the following:

    • Bitmap join, domain, or function-based indexes

    • UNION ALL or CONCATENATION

      (Either in the query itself, or as the result of an OR-expansion execution plan chosen by the query optimizer.)

    • ORDER BY or GROUP BY

      (Either in the query itself, or as the result of a SORT operation with an ORDER BY option in the execution plan chosen by the query optimizer.)

    • Partitioned index-organized table (IOT) with overflow segment

    • Clustered objects

    • Parallel execution

Queries that Cannot Be Registered for QRCN in Either Mode

A query that refers to any of the following cannot be registered for QRCN in either guaranteed or best-effort mode:

  • Views

  • Tables that are fixed, remote, or have Virtual Private Database (VPD) policies enabled

  • DUAL (in the SELECT list)

  • Synonyms

  • Calls to user-defined PL/SQL subprograms

  • Operators not listed in Queries that Can Be Registered for QRCN in Guaranteed Mode

  • The aggregate function COUNT

    (Other aggregate functions are allowed in best-effort mode, but not in guaranteed mode.)

  • Application contexts; for example:

    SELECT SALARY FROM EMPLOYEES
      WHERE USER = SYS_CONTEXT('USERENV', 'SESSION_USER');
    
  • SYSDATE, SYSTIMESTAMP, or CURRENT TIMESTAMP

Also, a query that the query optimizer has rewritten using a materialized view cannot be registered for QRCN. For information about the query optimizer, see Oracle Database Performance Tuning Guide.

Using PL/SQL to Register Queries for CQN

To use PL/SQL to create a CQN registration, follow these steps:

  1. Create a stored PL/SQL procedure to serve as the notification handler.

  2. Create a CQ_NOTIFICATION$_REG_INFO object that specifies the name of the notification handler, the notification type, and other attributes of the registration.

  3. In your client application, use the DBMS_CQ_NOTIFICATION.NEW_REG_START function to open a registration block.

  4. Execute the queries that you want to register. (Do not execute DML or DDL operations.)

  5. Close the registration block, using the DBMS_CQ_NOTIFICATION.REG_END function.

Topics:

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the CQ_NOTIFICATION$_REG_INFO object and the functions NEW_REG_START and REG_END, all of which are defined in the DBMS_CQ_NOTIFICATION package

Creating a PL/SQL Notification Handler

The PL/SQL stored procedure that you create to serve as the notification handler must have the following signature:

PROCEDURE schema_name.proc_name(ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)

In the preceding signature, schema_name is the name of the database schema, proc_name is the name of the stored procedure, and ntfnds is the notification descriptor.

The notification descriptor is a CQ_NOTIFICATION$_DESCRIPTOR object, whose attributes describe the details of the change (transaction ID, type of change, queries affected, tables modified, and so on).

The JOBQ process passes the notification descriptor, ntfnds, to the notification handler, proc_name, which handles the notification according to its application requirements. (This is step 6 in Figure 12-2.)

Note:

The notification handler executes inside a job queue process. The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs. You must set JOB_QUEUE_PROCESSES to a nonzero value to receive PL/SQL notifications.

Creating a CQ_NOTIFICATION$_REG_INFO Object

An object of type CQ_NOTIFICATION$_REG_INFO specifies the notification handler that the database executes when a registered objects changes. In SQL*Plus, you can view its type attributes by executing the following statement:

DESC CQ_NOTIFICATION$_REG_INFO

Table 12-2 describes the attributes of SYS.CQ_NOTIFICATION$_REG_INFO.

Table 12-2 Attributes of CQ_NOTIFICATION$_REG_INFO

Attribute Description

CALLBACK

Specifies the name of the PL/SQL procedure to be executed when a notification is generated (a notification handler). You must specify the name in the form schema_name.procedure_name, for example, hr.dcn_callback.

QOSFLAGS

Specifies one or more quality-of-service flags, which are constants in the DBMS_CQ_NOTIFICATION package. For their names and descriptions, see Table 12-3.

To specify more than one quality-of-service flag, use bitwise OR. For example: DBMS_CQ_NOTIFICATION.QOS_RELIABLE + DBMS_CQ_NOTIFICATION.QOS_ROWIDS

TIMEOUT

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 0 or NULL, then the registration persists until the client explicitly unregisters it.

Can be combined with the QOSFLAGS attribute with its QOS_DEREG_NFY flag.

OPERATIONS_FILTER

Applies only to OCN (described in Object Change Notification (OCN)). Has no effect if you specify the QOS_FLAGS attribute with its QOS_QUERY flag.

Filters messages based on types of SQL statement. You can specify the following constants in the DBMS_CQ_NOTIFICATION package:

  • ALL_OPERATIONS notifies on all changes

  • INSERTOP notifies on inserts

  • UPDATEOP notifies on updates

  • DELETEOP notifies on deletes

  • ALTEROP notifies on ALTER TABLE operations

  • DROPOP notifies on DROP TABLE operations

  • UNKNOWNOP notifies on unknown operations

You can specify a combination of operations with a bitwise OR. For example: DBMS_CQ_NOTIFICATION.INSERTOP + DBMS_CQ_NOTIFICATION.DELETEOP.

TRANSACTION_LAG

Deprecated. To implement flow-of-control notifications, use the NTFN_GROUPING_* attributes.

Applies only to OCN (described in Object Change Notification (OCN)). Has no effect if you specify the QOS_FLAGS attribute with its QOS_QUERY flag.

Specifies the number of transactions or database changes by which the client can lag behind the database. If 0, then the client receives an invalidation message as soon as it is generated. If 5, then every fifth transaction that changes a registered object results in a notification. The database tracks intervening changes at an object granularity and bundles the changes along with the notification. Thus, the client does not lose intervening changes.

Most applications that must be notified of changes to an object on transaction commit without further deferral are expected to chose 0 transaction lag. A nonzero transaction lag is useful only if an application implements 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 frequency. Otherwise, notifications might be deferred indefinitely till the lag is satisfied.

If you specify TRANSACTION_LAG, then the ROWID level granularity is not available in the notification messages even if you specified QOS_ROWIDS during registration.

NTFN_GROUPING_CLASS

Specifies the class by which to group notifications. Currently, the only allowed value is DBMS_CQ_NOTIFICATION.NTFN_GROUPING_CLASS_TIME, which groups notifications by time.

NTFN_GROUPING_VALUE

Specifies the time interval that defines the group, in seconds. For example, if this value is 900, notifications generated in the same 15-minute interval are grouped together.

NTFN_GROUPING_TYPE

Specifies either of the following types of grouping:

  • DBMS_CQ_NOTIFICATION.NTFN_GROUPING_TYPE_SUMMARY: All notifications in the group are summarized into a single notification.

  • DBMS_CQ_NOTIFICATION.NTFN_GROUPING_TYPE_LAST: Only the last notification in the group is published and the earlier ones discarded.

NTFN_GROUPING_START_TIME

Specifies when to start generating notifications. If specified as NULL, it defaults to the current system-generated time.

NTFN_GROUPING_REPEAT_COUNT

Specifies how many times to repeat the notification. Set to DBMS_CQ_NOTIFICATION.NTFN_GROUPING_FOREVER to receive notifications for the life of the registration. To receive at most n notifications during the life of the registration, set to n.


The quality-of-service flags in Table 12-3 are constants in the DBMS_CQ_NOTIFICATION package. You can specify them with the QOS_FLAGS attribute of CQ_NOTIFICATION$_REG_INFO (see Table 12-2).

Table 12-3 Quality-of-Service Flags

Flag Description

QOS_DEREG_NFY

Purges the registration after the first notification.

QOS_RELIABLE

Stores notifications in a persistent database queue.

In an Oracle RAC environment, if a database instance fails, surviving database instances can deliver any queued notification messages.

Default: Notifications are stored in shared memory, which performs more efficiently.

QOS_ROWIDS

Includes the ROWID of each changed row in the notification.

QOS_QUERY

Registers queries for QRCN, described in Query Result Change Notification (QRCN).

If a query cannot be registered for QRCN, an error is generated at registration time, unless you also specify QOS_BEST_EFFORT.

Default: Queries are registered for OCN, described in Object Change Notification (OCN)

QOS_BEST_EFFORT

Used with QOS_QUERY. Registers simplified versions of queries that are too complex for query result change evaluation; in other words, registers queries for QRCN in best-effort mode, described in Best-Effort Mode.

To see which queries were simplified, query the static data dictionary view DBA_CQ_NOTIFICATION_QUERIES or USER_CQ_NOTIFICATION_QUERIES. These views give the QUERYID and the text of each registered query.

Default: Queries are registered for QRCN in guaranteed mode, described in Guaranteed Mode


Suppose that you want to invoke the procedure HR.dcn_callback whenever a registered object changes. In Example 12-4, you create a CQ_NOTIFICATION$_REG_INFO object that specifies that HR.dcn_callback receives notifications. To create the object you must have EXECUTE privileges on the DBMS_CQ_NOTIFICATION package.

Example 12-4 Creating a CQ_NOTIFICATION$_REG_INFO Object

DECLARE
  v_cn_addr CQ_NOTIFICATION$_REG_INFO;

BEGIN
  -- Create object:

  v_cn_addr := CQ_NOTIFICATION$_REG_INFO (
    'HR.dcn_callback',                 -- PL/SQL notification handler
    DBMS_CQ_NOTIFICATION.QOS_QUERY     -- notification type QRCN
    + DBMS_CQ_NOTIFICATION.QOS_ROWIDS, -- include rowids of changed objects
    0,                          -- registration persists until unregistered
    0,                          -- notify on all operations
    0                           -- notify immediately
    );

  -- Register queries:
  ...
END;
/

Identifying Individual Queries in a Notification

Any query in a registered list of queries can cause a continuous query notification. If you want to know when a certain query causes a notification, use the DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID function in the SELECT list of that query. For example:

SELECT EMPLOYEE_ID, SALARY, DBMS_CQ_NOTIFICATION.CQ_NOTIFICATION_QUERYID
  FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 10;

When that query causes a notification, the notification includes the query ID.

Adding Queries to an Existing Registration

To add queries to an existing registration, follow these steps:

  1. Retrieve the registration ID of the existing registration.

    You can retrieve it from either saved SQL*Plus output or a query of *_CHANGE_NOTIFICATION_REGS.

  2. Open the existing registration by calling the procedure DBMS_CQ_NOTIFICATION.ENABLE_REG with the registration ID as the parameter.

  3. Execute the queries that you want to register. (Do not execute DML or DDL operations.)

  4. Close the registration, using the DBMS_CQ_NOTIFICATION.REG_END function.

Example 12-5 adds a query to an existing registration whose registration ID is 21.

Example 12-5 Adding a Query to an Existing Registration

DECLARE
  v_cursor SYS_REFCURSOR;

BEGIN
  -- Open existing registration
  DBMS_CQ_NOTIFICATION.ENABLE_REG(21);
  OPEN v_cursor FOR
    -- Execute query to be registered
    SELECT DEPARTMENT_ID
      FROM HR.DEPARTMENTS;  -- register this query
  CLOSE v_cursor;
  -- Close registration
  DBMS_CQ_NOTIFICATION.REG_END;
END;
/

Best Practices for CQN Registrations

For best CQN performance, follow these registration guidelines:

  • Register few queries—preferably those that reference objects that rarely change.

    Extremely volatile registered objects cause numerous notifications, whose overhead slows OLTP throughput.

  • Minimize the number of duplicate registrations of any given object, in order to avoid replicating a notification message for multiple recipients.

Troubleshooting CQN Registrations

If you are unable to create a registration, or if you have created a registration but are not receiving the notifications that you expected, the problem might be one of the following:

  • The JOB_QUEUE_PROCESSES parameter is not set to a nonzero value.

    This prevents you from receiving PL/SQL notifications through the notification handler.

  • You were connected as a SYS user when you created the registrations.

    You must be connected as a non-SYS user in order to create CQN registrations.

  • You changed a registered object, but did not commit the transaction.

    Notifications are generated only when the transaction commits.

  • The registrations were not successfully created in the database.

    To check, query the static data dictionary view *_CHANGE_NOTIFICATION_REGS. For example, the following statement displays all registrations and registered objects for the current user:

    SELECT REGID, TABLE_NAME FROM USER_CHANGE_NOTIFICATION_REGS;
    
  • Run-time errors occurred during the execution of the notification handler.

    If so, they were logged to the trace file of the JOBQ process that tried to execute the procedure. The name of the trace file usually has the following form:

    ORACLE_SID_jnumber_PID.trc
    

    For example, if the ORACLE_SID is dbs1 and the process ID (PID) of the JOBQ process is 12483, the name of the trace file is usually dbs1_j000_12483.trc.

    Suppose that a registration is created with 'chnf_callback' as the notification handler and registration ID 100. Suppose that 'chnf_callback' was not defined in the database. Then the JOBQ trace file might contain a message of the form:

    ****************************************************************************
       Run-time 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 run-time errors occurred during the execution of the notification handler, create a very simple version of the notification handler to verify that you are actually receiving notifications, and then gradually add application logic.

    An example of a very simple notification handler is:

    REM Create table in HR schema to hold count of notifications received.
    CREATE TABLE nfcount(cnt NUMBER);
    INSERT INTO nfcount VALUES(0);
    COMMIT;
    CREATE OR REPLACE PROCEDURE chnf_callback
      (ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR)
    IS
    BEGIN
      UPDATE nfcount SET cnt = cnt+1;
      COMMIT;
    END;
    /
    
  • There is a time lag between the commit of a transaction and the notification received by the end user.

Querying CQN Registrations

To see top-level information about all registrations, including their QOS options, query one of the static data dictionary views *_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:

SELECT regid, table_name FROM USER_CHANGE_NOTIFICATION_REGS;

To see which queries are registered for QRCN, query the static data dictionary view USER_CQ_NOTIFICATION_QUERIES or DBA_CQ_NOTIFICATION_QUERIES. These views include information about any bind values that the queries use. In these views, bind values in the original query are included in the query text as constants. The query text is equivalent, but maybe not identical, to the original query that was registered.

See Also:

Oracle Database Reference for more information about the static data dictionary views USER_CHANGE_NOTIFICATION_REGS and DBA_CQ_NOTIFICATION_QUERIES

Interpreting Notifications

When a transaction commits, the database determines whether registered objects were modified in the transaction. If so, it executes the notification handler specified in the registration.

Topics:

Interpreting a CQ_NOTIFICATION$_DESCRIPTOR Object

When a CQN registration generates a notification, the database passes a CQ_NOTIFICATION$_DESCRIPTOR object to the notification handler. The notification handler can find the details of the database change in the attributes of the CQ_NOTIFICATION$_DESCRIPTOR object.

In SQL*Plus, you can list these attributes by connecting as SYS and executing the following statement:

DESC CQ_NOTIFICATION$_DESCRIPTOR

Table 12-4 summarizes the attributes of CQ_NOTIFICATION$_DESCRIPTOR.

Table 12-4 Attributes of CQ_NOTIFICATION$_DESCRIPTOR

Attribute Description

REGISTRATION_ID

The registration ID that was returned during registration.

TRANSACTION_ID

The ID for the transaction that made the change.

DBNAME

The name of the database in which the notification was generated.

EVENT_TYPE

The database event that triggers a notification. For example, the attribute can contain the following constants, which correspond to different database events:

  • EVENT_NONE

  • EVENT_STARTUP (Instance startup)

  • EVENT_SHUTDOWN (Instance shutdown - last instance shutdown in the case of Oracle RAC)

  • EVENT_SHUTDOWN_ANY (Any instance shutdown in the case of Oracle RAC)

  • EVENT_DEREG (Registration was removed)

  • EVENT_OBJCHANGE (Change to a registered table)

  • EVENT_QUERYCHANGE (Change to a registered result set)

NUMTABLES

The number of tables that were modified.

TABLE_DESC_ARRAY

This field is present only for OCN registrations. For QRCN registrations, it is NULL.

If EVENT_TYPE is EVENT_OBJCHANGE]: a VARRAY of table change descriptors of type CQ_NOTIFICATION$_TABLE, each of which corresponds to a changed table. For attributes of CQ_NOTIFICATION$_TABLE, see Table 12-5.

Otherwise: NULL.

QUERY_DESC_ARRAY

This field is present only for QRCN registrations. For OCN registrations, it is NULL.

If EVENT_TYPE is EVENT_QUERYCHANGE]: a VARRAY of result set change descriptors of type CQ_NOTIFICATION$_QUERY, each of which corresponds to a changed result set. For attributes of CQ_NOTIFICATION$_QUERY, see Table 12-6.

Otherwise: NULL.


Interpreting a CQ_NOTIFICATION$_TABLE Object

The CQ_NOTIFICATION$_DESCRIPTOR type contains an attribute called TABLE_DESC_ARRAY, which holds a VARRAY of table descriptors of type CQ_NOTIFICATION$_TABLE.

In SQL*Plus, you can list these attributes by connecting as SYS and executing the following statement:

DESC CQ_NOTIFICATION$_TABLE

Table 12-5 summarizes the attributes of CQ_NOTIFICATION$_TABLE.

Table 12-5 Attributes of CQ_NOTIFICATION$_TABLE

Attribute Specifies . . .

OPFLAGS

The type of operation performed on the modified table. For example, the attribute can contain the following constants, which correspond to different database operations:

  • ALL_ROWS signifies that either the entire table is modified, as in a DELETE *, or row-level granularity of information is not requested or not available in the notification, and the recipient must assume that the entire table has changed

  • UPDATEOP signifies an update

  • DELETEOP signifies a deletion

  • ALTEROP signifies an ALTER TABLE

  • DROPOP signifies a DROP TABLE

  • UNKNOWNOP signifies an unknown operation

TABLE_NAME

The name of the modified table.

NUMROWS

The number of modified rows.

ROW_DESC_ARRAY

A VARRAY of row descriptors of type CQ_NOTIFICATION$_ROW, which is described in Table 12-7. If ALL_ROWS was set in the opflags, then the ROW_DESC_ARRAY member is NULL.


Interpreting a CQ_NOTIFICATION$_QUERY Object

The CQ_NOTIFICATION$_DESCRIPTOR type contains an attribute called QUERY_DESC_ARRAY, which holds a VARRAY of result set change descriptors of type CQ_NOTIFICATION$_QUERY.

In SQL*Plus, you can list these attributes by connecting as SYS and executing the following statement:

DESC CQ_NOTIFICATION$_QUERY

Table 12-6 summarizes the attributes of CQ_NOTIFICATION$_QUERY.

Table 12-6 Attributes of CQ_NOTIFICATION$_QUERY

Attribute Specifies . . .

QUERYID

Query ID of the changed query.

QUERYOP

Operation that changed the query (either EVENT_QUERYCHANGE or EVENT_DEREG).

TABLE_DESC_ARRAY

A VARRAY of table change descriptors of type CQ_NOTIFICATION$_TABLE, each of which corresponds to a changed table that caused a change in the result set. For attributes of CQ_NOTIFICATION$_TABLE, see Table 12-5.


Interpreting a CQ_NOTIFICATION$_ROW Object

If the ROWID option was specified during registration, the CQ_NOTIFICATION$_TABLE type has a ROW_DESC_ARRAY attribute, a VARRAY of type CQ_NOTIFICATION$_ROW that contains the ROWIDs for the changed rows. If ALL_ROWS was set in the OPFLAGS field of the CQ_NOTIFICATION$_TABLE object, then ROWID information is not available.

Table 12-7 summarizes the attributes of CQ_NOTIFICATION$_ROW.

Table 12-7 Attributes of CQ_NOTIFICATION$_ROW

Attribute Specifies . . .

OPFLAGS

The type of operation performed on the modified table. See the description of OPFLAGS in Table 12-5.

ROW_ID

The ROWID of the changed row.


Deleting Registrations

To delete a registration, call the procedure DBMS_CQ_NOTIFICATION.DEREGISTER with the registration ID as the parameter. For example, the following statement deregisters the registration whose registration ID is 21:

DBMS_CQ_NOTIFICATION.DEREGISTER(21);

Only the user who created the registration or the SYS user can deregister it.

Configuring CQN: Scenario

In this scenario, you are a developer who manages a Web application that provides employee data: name, location, phone number, and so on. 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 the query results. Caching avoids a round trip to the back-end database as well as server-side execution latency.

You can use the DBMS_CQ_NOTIFICATION package to register queries based on HR.EMPLOYEES and HR.DEPARTMENTS tables. To configure CQN, you follow these steps:

  1. Create a server-side PL/SQL stored procedure to process the notifications, as instructed in Creating a PL/SQL Notification Handler.

  2. Register the queries on the HR.EMPLOYEES and HR.DEPARTMENTS tables for QRCN, as instructed in Registering the Queries.

After you complete these steps, any committed change to the result of a query registered in step 2 causes the notification handler created in step 1 to notify the Web application of the change, whereupon the Web application refreshes the cache by querying the back-end database.

Topics:

Creating a PL/SQL Notification Handler

Create a a server-side stored PL/SQL procedure to process notifications as follows:

  1. Connect to the database AS SYSDBA.

  2. Grant the required privileges to HR:

    GRANT EXECUTE ON DBMS_CQ_NOTIFICATION TO HR;
    GRANT CHANGE NOTIFICATION TO HR;
    
  3. Enable the JOB_QUEUE_PROCESSES parameter to receive notifications:

    ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;
    
  4. Connect to the database as a non-SYS user (such as HR).

  5. Create database tables to hold records of notification events received:

    REM Create a table to record notification events.
    CREATE TABLE nfevents (
      regid      NUMBER,
      event_type NUMBER   );
    
    REM Create a table to record notification queries.
    CREATE TABLE nfqueries (
      qid NUMBER,
      qop NUMBER           );
    
    REM Create a table to record changes to registered tables.
    CREATE TABLE nftablechanges (
      qid             NUMBER,
      table_name      VARCHAR2(100),
      table_operation NUMBER    );
    
    REM Create a table to record ROWIDs of changed rows.
    CREATE TABLE nfrowchanges (
      qid        NUMBER,
      table_name VARCHAR2(100),
      row_id     VARCHAR2(2000));
    
  6. Create the procedure HR.chnf_callback, as shown in Example 12-6.

Example 12-6 Creating Server-Side PL/SQL Notification Handler

CREATE OR REPLACE PROCEDURE chnf_callback (
  ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR   )
IS
  regid           NUMBER;
  tbname          VARCHAR2(60);
  event_type      NUMBER;
  numtables       NUMBER;
  operation_type  NUMBER;
  numrows         NUMBER;
  row_id          VARCHAR2(2000);
  numqueries      NUMBER;
  qid NUMBER;
  qop NUMBER;

BEGIN
  regid := ntfnds.registration_id;
  event_type := ntfnds.event_type;
  INSERT INTO nfevents VALUES(regid, event_type);
  numqueries :=0;

  IF (event_type = DBMS_CQ_NOTIFICATION.EVENT_QUERYCHANGE) THEN
    numqueries := ntfnds.query_desc_array.count;
    FOR i IN 1..numqueries LOOP
      qid := ntfnds.query_desc_array(i).queryid;
      qop := ntfnds.query_desc_array(i).queryop;
      INSERT INTO nfqueries VALUES(qid, qop);
      numtables := 0;
      numtables := ntfnds.query_desc_array(i).table_desc_array.count;
      FOR j IN 1..numtables LOOP
        tbname :=
          ntfnds.query_desc_array(i).table_desc_array(j).table_name;
        operation_type :=
          ntfnds.query_desc_array(i).table_desc_array(j).Opflags;
        INSERT INTO nftablechanges VALUES(qid, tbname, operation_type);
        IF (bitand(operation_type, DBMS_CQ_NOTIFICATION.ALL_ROWS) = 0)
        THEN
          numrows := ntfnds.query_desc_array(i).table_desc_array(j).numrows;
        ELSE
          numrows :=0;  -- ROWID info not available
        END IF;

        /* Body of loop does not execute when numrows is zero */
        FOR k IN 1..numrows LOOP
          Row_id :=
 ntfnds.query_desc_array(i).table_desc_array(j).row_desc_array(k).row_id;
          INSERT INTO nfrowchanges VALUES(qid, tbname, Row_id);
        END LOOP;  -- loop over rows
      END LOOP;  -- loop over tables
    END LOOP;  -- loop over queries
  END IF;
  COMMIT;
END;
/

Registering the Queries

After creating the notification handler, you register the queries for which you want to receive notifications, specifying HR.chnf_callback as the notification handler, as in Example 12-7.

Example 12-7 Registering a Query

DECLARE
  reginfo  CQ_NOTIFICATION$_REG_INFO;
  mgr_id   NUMBER;
  dept_id  NUMBER;
  v_cursor SYS_REFCURSOR;
  regid    NUMBER;

BEGIN
  /* Register two queries for QRNC: */
  /* 1. Construct registration information.
        chnf_callback is name of notification handler.
        QOS_QUERY specifies result-set-change notifications. */

  reginfo := cq_notification$_reg_info (
    'chnf_callback',
    DBMS_CQ_NOTIFICATION.QOS_QUERY,
    0, 0, 0                            );

  /* 2. Create registration. */

  regid := DBMS_CQ_NOTIFICATION.new_reg_start(reginfo);

  OPEN v_cursor FOR
    SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, manager_id
      FROM HR.EMPLOYEES
        WHERE employee_id = 7902;
  CLOSE v_cursor;

  OPEN v_cursor FOR
    SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, department_id
      FROM HR.departments
        WHERE department_name = 'IT';
  CLOSE v_cursor;
END;
/

You can view the newly created registration by issuing the following query:

SELECT queryid, regid, TO_CHAR(querytext)
   FROM user_cq_notification_queries;

The result of the preceding query has the following information:

QUERYID REGID                               TO_CHAR(QUERYTEXT)
------- ----- ------------------------------------------------
     22    41 SELECT HR.DEPARTMENTS.DEPARTMENT_ID
                FROM HR.DEPARTMENTS
                  WHERE HR.DEPARTMENTS.DEPARTMENT_NAME  = 'IT'

     21    41 SELECT HR.EMPLOYEES.MANAGER_ID
                FROM HR.EMPLOYEES
                  WHERE HR.EMPLOYEES.EMPLOYEE_ID  = 7902

Execute the following transaction, which changes the result of the query with QUERYID 22:

UPDATE DEPARTMENTS SET DEPARTMENT_NAME = 'FINANCE'
  WHERE department_name = 'IT';
COMMIT;

The notification procedure chnf_callback (which you created in Example 12-6) executes.

Now query the table in which notification events are recorded:

SQL> SELECT * FROM nfevents;

The result of the preceding query has the following information:

REGID EVENT_TYPE
----- ----------
   61          7

EVENT_TYPE 7 corresponds to EVENT_QUERYCHANGE (query result change).

Query the table in which changes to registered tables are recorded:

SELECT * FROM nftablechanges;

The result of the preceding query has the following information:

REGID     TABLE_NAME TABLE_OPERATION
----- -------------- ---------------
   42 HR.DEPARTMENTS               4

TABLE_OPERATION 4 corresponds to UPDATEOP (update operation).

Query the table in which ROWIDs of changed rows are recorded:

SELECT * FROM nfrowchanges;

The result of the preceding query has the following information:

REGID     TABLE_NAME              ROWID
----- -------------- ------------------
   61 HR.DEPARTMENTS AAANkdAABAAALinAAF

TABLE_OPERATION 4 corresponds to UPDATEOP (update operation).