Skip Headers
Oracle® Data Provider for .NET Developer's Guide
11g Release 1 (11.1)

B28375-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Database Change Notification Support

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

Note:

The ODP.NET Database Change Notification feature uses the Continuous Query Notification feature in the Oracle database.

Note:

Database Change Notification is not supported in a .NET stored procedure.

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.

See Also:

Oracle Database Advanced Application Developer's Guide for further information on Continuous Query Notification

By default, Windows Vista and Windows XP Service Pack 2 and higher enable the Windows Firewall to block virtually all TCP network ports to incoming connections. Therefore, for Continuous Query Notification to work properly on these operating systems, the Windows Firewall must be configured properly to allow specific executables to open specific ports.

See Also:

Oracle Database Platform Guide for Windows for details on configuring the Windows Firewall

Beginning with Oracle Database 11g and ODP.NET 11g (11.1), Database Change 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 notifications are supported only when all the following are true:

  1. The Oracle database version is at least 11.1.

  2. The select list contains no other column data types other than VARCHAR2 and NUMBER.

  3. The COMPATIBLE initialization parameter of the database is set to at least 11.0.0 and Automatic Undo Management (AUM) is enabled (the default).

If 1) is not met, the notification is registered as object-based for backward compatibility.If 2) and other documented restrictions are not met, the notification is registered as object-based since ODP.NET uses the best-effort mode.If 3) is not met, an error is returned upon registration.For further details on the requirements for query-based change notification, please read the chapter "Using Continuous Query Notification" in Oracle Database Advanced Application Developer's Guide.

This section contains the following topics:

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.

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.

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 database change 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 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.

See Also:

Oracle Database Advanced Application Developer's Guide for further information on Database Change Notification