Continuous Query Notification Support

Oracle Data Provider for .NET provides a notification framework that supports Continuous Query Notification, enabling applications to receive client-side notifications when there is a change in a query result set, schema objects, or the state of the database, even if no Oracle Data Provider for .NET database connection exists. Using Continuous Query Notification, an application can maintain the validity of the client-side cache (for example, the ADO.NET DataSet) easily. Continuous Query Notification was previously known as Database Change Notification.

Using the notification framework, applications can specify a query result set as a registered query for notification request on the database, and create this notification registration to maintain the validity of the query result set. When there is a change on the database that could affect the client-side cache’s query results, the notification framework notifies the application.

Note: The content of a change notification is referred to as an invalidation message. It indicates that the query result set is now invalid and provides information about the changes.

Based on the information provided by the invalidation message, the application can then act accordingly. For example, the application might need to refresh its own copy of the data for the registered query that is stored locally in the application.

Note: If a registered object is dropped from the database and a new one is created with the same name in the same schema, re-registration is required to receive notifications for the newly created object.

Firewalls, such as Windows Firewall, may be set up to block TCP network ports, which blocks incoming database notifications. Ensure the firewall is configured so that database applications can use the designated port for Continuous Query Notification. If the firewall configuration cannot be changed to allow notifications be sent to the client, consider enabling Client Initiated Continuous Query Notifications.

Continuous Query Notification queries can be query-based (default) or object-based. The query-based registrations allow ODP.NET to notify applications when the selected rows have changed in the database. The object-based registrations allow ODP.NET to notify applications for any changes that occur in the table(s) containing the selected rows.

Query-based registrations have two modes: guaranteed mode and best-effort mode. In guaranteed mode, any continuous query notification ensures that a change occurred to something contained in the queried result set. However, if a query is complex, then it cannot be registered in guaranteed mode. Best-effort mode is used in such cases.

Best-effort mode simplifies the query for query-based registration. No notifications are lost from the simplification. However, the simplification may cause false positives, as the simpler version’s query result could change when the original query result would not.There still remain some restrictions on which queries can have best-effort mode query-based registrations. In such cases, developers can use object-based registrations, which can register most query types. Object-based registrations generate notifications when the query object changes, even if the actual query result does not. This also means that object-based registrations are more prone to false positives than query-based registrations. Developers should be aware of the relative strengths and weaknesses of each continuous query notification option and choose the one that best suits their requirements.

If a large number of rows are modified at once, consuming significant shared pool resources, the application will not receive any change notifications with specific row information that had undergone changes. Rather, it will receive a notification with OracleNotificationEventArgs.Info property set to OracleNotificationInfo.Error.

This section contains the following topics:

See Also:

Client Initiated Continuous Query Notifications

ODP.NET 21c introduces support for Client Initiated Continuous Query Notifications (CICQN). Traditional Continuous Query Notification (CQN) communicates database change notifications back to clients using a listening end point, a client’s IP/hostname and a port. In cloud deployments or when firewalls between the database server and client cannot be configured to permit messages to the listening end point, these notifications will be blocked.

CICQN uses a dedicated connection instead of the listening end point. This connection aggregates all the database change notifications for the pool’s users. ODP.NET attempts to always keep this connection open. It does not count toward Min Pool Size and Max Pool Size limits.

ODP.NET 23ai supports CICQN with Oracle Database 19c and higher. ODP.NET 21c CICQN requires Oracle Database 21c or higher. It can be enabled by setting the OracleConfiguration.UseClientInitiatedCQN static Boolean property or .NET Framework configuration file UseClientInitiatedCQN setting to true. By default, it is false.

For most on-premises deployments, traditional CQN is recommended.

CICQN is supported exclusively over the TCP protocol; TCPS is not supported directly for the background notification connection.

In TCPS client connectivity environments, a separate TCP-based connection path must be established for CICQN. The protocol switching capability of Oracle Connection Manager (CMAN) can be used to bridge this gap. CMAN can be configured to accept incoming TCPS connections from the client and forward them to the database over TCP.

Regular connections can continue to interface with the database through the TCPS protocol. In Oracle Autonomous Database environments, Oracle Connection Manager is pre-integrated; therefore, no additional user configuration is required.

Continuous Query Notification Classes

The following classes are associated with Continuous Query Notification Support:

Supported Operations

The ODP.NET notification framework in conjunction with Continuous Query Notification supports the following activities:

See Also:

Requirements of Notification Registration

The connected user must have the CHANGE NOTIFICATION privilege to create a notification registration.

This SQL statement grants the CHANGE NOTIFICATION privilege:

grant change notification to user name

This SQL statement revokes the CHANGE NOTIFICATION privilege:

revoke change notification from user name

Using Continuous Query Notification

This section describes what the application should do, and the flow of the process, when an application uses Continuous Query Notification to receive notifications for any changes in the registered query result set.

Application Steps

The application should do the following:

  1. Create an OracleDependency instance.

  2. Assign an event handler to the OracleDependency.OnChange event property if the application wishes to have an event handler invoked when database changes are detected. Otherwise, the application can choose to poll on the HasChanges property of the OracleDependency object. This event handler is invoked when the change notification is received.

  3. Set the port number for the listener to listen on. The application can specify the port number for one notification listener to listen on. If the application does not specify a port number, a random one is used by the listener.

  4. Bind the OracleDependency instance to an OracleCommand instance that contains the actual query to be executed. Internally, the Continuous Query Notification request (an OracleNotificationRequest instance) is created and assigned to the OracleCommand.Notification property.

Flow of Notification Process

  1. When the command associated with the notification request is executed, the notification registration is created in the database. The command execution must return a result set, or contain one or more REF cursors for a PL/SQL stored procedure.

  2. ODP.NET starts the application listener on the first successful notification registration.

  3. When a change related to the registration occurs in the database, the application is notified through the event delegate assigned to the OracleDependency.OnChange event property, or the application can poll the OracleDependency.HasChanges property.

The following example demonstrates the continuous query notification feature.

// Database Setup
// NOTE: unless the following SQL command is executed,
// ORA-29972 will be obtained from running this sample
/*
grant change notification to scott;
*/
using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

//This sample shows the continuous query notification feature in ODP.NET.
//Application specifies to get a notification when emp table is updated.
//When emp table is updated, the application will get a notification
//through an event handler.
namespace NotificationSample
{
  public class MyNotificationSample
  {
    public static bool IsNotified = false;

    public static void Main(string[] args)
    {
      //To Run this sample, make sure that the change notification privilege
      //is granted to scott.
      string constr = "User Id=scott;Password=tiger;Data Source=oracle";
      OracleConnection con = null;
      OracleDependency dep = null;

      try
      {
        con = new OracleConnection(constr);
        OracleCommand    cmd = new OracleCommand("select * from emp", con);
        con.Open();

        // Set the port number for the listener to listen for the notification
        // request
        OracleDependency.Port = 1005;

        // Create an OracleDependency instance and bind it to an OracleCommand
        // instance.
        // When an OracleDependency instance is bound to an OracleCommand
        // instance, an OracleNotificationRequest is created and is set in the
        // OracleCommand's Notification property. This indicates subsequent
        // execution of command will register the notification.
        // By default, the notification request is using the Database Change
        // Notification.
        dep = new OracleDependency(cmd);

        // Add the event handler to handle the notification. The
        // OnMyNotification method will be invoked when a notification message
        // is received from the database
        dep.OnChange +=
          new OnChangeEventHandler(MyNotificationSample.OnMyNotificaton);

        // The notification registration is created and the query result sets
        // associated with the command can be invalidated when there is a
        // change.  When the first notification registration occurs, the
        // notification listener is started and the listener port number
        // will be 1005.
        cmd.ExecuteNonQuery();

        // Updating emp table so that a notification can be received when
        // the emp table is updated.
        // Start a transaction to update emp table
        OracleTransaction txn = con.BeginTransaction();
        // Create a new command which will update emp table
        string updateCmdText =
          "update emp set sal = sal + 10 where empno = 7782";
        OracleCommand updateCmd = new OracleCommand(updateCmdText, con);
        // Update the emp table
        updateCmd.ExecuteNonQuery();
        //When the transaction is committed, a notification will be sent from
        //the database
        txn.Commit();
      }
      catch (Exception e)
      {
        Console.WriteLine(e.Message);
      }

      con.Close();
      // Loop while waiting for notification
      while(MyNotificationSample.IsNotified == false)
      {
        Thread.Sleep(100);
      }
    }

    public static void OnMyNotificaton(object src,
      OracleNotificationEventArgs arg)
    {
      Console.WriteLine("Notification Received");
      DataTable changeDetails = arg.Details;
      Console.WriteLine("Data has changed in {0}",
        changeDetails.Rows[0]["ResourceName"]);
      MyNotificationSample.IsNotified = true;
    }
  }
}

Best Practice Guidelines and Performance Considerations

This section provides guidelines for working with Continuous Query Notification and the ODP.NET notification framework, and discusses the performance impacts.Every change notification registration consumes database memory, storage or network resources, or some combination thereof. The resource consumption further depends on the volume and size of the invalidation message. In order to scale well with a large number of mid-tier clients, Oracle recommends that the client implement these best practices: