Database Change Notification Support

Database Change Notification Classes

The following classes are associated with Continuous Query Notification Support:

• OracleDependency

  Represents a dependency between an application and an Oracle database based on the database events which the application is interested in. It contains information about the dependency and provides the mechanism to notify the application when specified database events occurs. The OracleDependency class is also responsible for creating the notification listener to listen for database notifications. There is only one database notification listener for each application domain. This notification listener terminates when the application process terminates.

  The dependency between the application and the database is not established when the OracleDependency object is created. The dependency is established when the command that is associated with this OracleDependency object is executed. That command execution creates a database change notification registration in the database.

  When a change has occurred in the database, the HasChanges property of the OracleDependency object is set to true. Furthermore, if an event handler was registered with the OnChange event of the OracleDependency object, the registered event handler function will be invoked.

• OracleNotificationRequest

  Represents a notification request to be registered in the database. It contains information about the request and the properties of the notification.

• OracleNotificationEventArgs

  Represents the invalidation message generated for a notification when a specified database event occurs and contains details about that database event.

  See Also:

  • "OracleDependency Class"

  • "OracleNotificationRequest Class"

  • "OracleNotificationEventArgs Class"

Supported Operations

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

• Creating a notification registration by:

  • Creating an OracleDependency instance and binding it to an OracleCommand instance.

• Grouping multiple notification requests into one registration by:

  • Using the OracleDependency.AddCommandDependency method.

  • Setting the OracleCommand.Notification request using the same OracleNotificationRequest instance.

• Registering for Database Change Notification by:

  • Executing the OracleCommand. If either the notification property is null or NotificationAutoEnlist is false, the notification will not be made.

• Removing notification registration by:

  • Using the OracleDependency.RemoveRegistration method.

  • Setting the Timeout property in the OracleNotificationRequest instance before the registration is created.

  • Setting the IsNotifiedOnce property to true in the OracleNotificationRequest instance before the registration is created. The registration is removed once a database notification is sent.

• Ensuring Change Notification Persistence by:

  • Specifying whether or not the invalidation message is queued persistently in the database before delivery. If an invalidation message is to be stored persistently in the database, then the change notification is guaranteed to be sent. If an invalidation message is stored in an in-memory queue, the change notification can be received faster, however, it could be lost upon database shutdown or crashes.

• Retrieving notification information including:

  • The changed object name.

  • The schema name of the changed object.

  • Database events that cause the notification, such as insert, delete, and so on.

  • The RowID of the modified object row.

    In Oracle SQL, the ROWIDTOCHAR(ROWID) and ROWIDTONCHAR(ROWID) functions convert a ROWID value to VARCHAR2 and NVARCHAR data types, respectively. If these functions are used within a SQL statement, ROWIDs are not returned in the OracleNotificationEventArgs object that is passed to the database change notification callback.

• Defining the listener port number.

  By default, the static OracleDependency.Port property is set to -1. This indicates that the ODP.NET listens on a port that is randomly picked when ODP.NET registers a database change notification request for the first time during the execution of an application.

  ODP.NET creates only one listener that listens on one port within an application domain. Once ODP.NET starts the listener, the port number cannot be changed; Changes to the static OracleDependency.Port property will generate an error if a listener has already been created.

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 Database Change 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.

// 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 database change 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:

• Few and mostly read-only tables

  There should be few registered objects, and these should be mostly read-only, with very infrequent invalidations. If an object is extremely volatile, then a large number of invalidation notifications are sent, potentially requiring a lot of space (in memory or on disk) in the invalidation queue. This is also true if a large number of objects are registered.

• Few rows updated for each table

  Transactions should update (or insert or delete) only a small number of rows within the registered tables. Depending on database resources, a whole table could be invalidated if too many rows are updated within a single transaction, for a given table.

  This policy helps to contain the size of a single invalidation message, and reduces disk storage for the invalidation queue.