Following the Changes, Part One


Use database change notification with ODP.NET and Oracle Database 10g Release 2.

By Mark A. Williams Oracle ACE

March/April 2006


There are different ways to access data from Oracle databases in .NET applications, but in terms of features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice.

This ODP.NET column is the first in a two-part examination of the new database change notification feature, introduced in Oracle Database 10g Release 2. This column introduces basic database change notification concepts and creates sample code to explore these concepts.

The Need for Database Change Notification

An important performance feature of modern applications—especially multitier, Web-based applications—is the use of a data cache. A data cache is typically used to remove or reduce excessive (and expensive) round-trip requests to the database server for data that has not changed. For example, when implementing a data sorting routine in your code, you sort the data in the cache rather than fetch the entire result set from the database.

However, when you're using a data cache, what does your application do when the data on the server does change? Two manual approaches are commonly used today. One approach is to give users a way to manually refresh the data in the cache. They click a Refresh button in your application, for example. Another approach is to poll the database at specific intervals or during each request to check whether the data has changed. Code that polls the database often retrieves a result set and compares it with the cached result set.

Both of these approaches have fairly significant limitations.

The limitation of the polling method is that if the polling interval is too small, it may unnecessarily produce too much database traffic. If the polling interval is too large, the end user will more likely be working with out-of-date data. Predicting the right balance is nearly impossible when workloads change over time.

Requiring users to click a Refresh button to refresh the data has the same limitations as the polling method, with the added limitation that the users must remember to refresh their data.

Database Change Notification Primer

With the introduction of Oracle Database 10g Release 2, you have a new option that addresses the limitations inherent in previous approaches to dealing with changing data: database change notification. When you use database change notification, the database server will notify you automatically when an event occurs that changes objects associated with a specific query.

Using the database change notification feature is a three-step process:

1. Registration: During the registration process, you specify a query that the database should watch for changes. ODP.NET automatically registers the events to watch for, based on the query. The database watches for Data Manipulation Language (DML) events, Data Definition Language (DDL) events, and global events. (A DML event occurs when the underlying data of a query is changed. A DDL event occurs when the structure of an object in the query is changed. A global event occurs when an action with a greater scope than the query alone takes place—the database is shut down, for example.)

2. Notification: Once a query has been registered with the database for change notification, you specify how you would like to receive that notification. You can receive the notification—automatically from the database—as an event in your application code, or you can poll the database. Most database change notification applications have the database automatically alert end users about changes, rather than using polling. (Note that ODP.NET needs to open a client network port to listen for the notification message from the database.)

3. Response: Your application responds to the change notification by taking some action, as appropriate. In most cases, you'll automatically update the cached data without requiring end user interaction. Alternatively, you can notify the user that the data has changed and ask if the user would like to update the cached data.

Database Change Notification in Action

Using the database change notification feature from an ODP.NET application is simple, and the data provider handles the underlying details of the process. To get started, you can use this month's sample code in Listing 1. This code uses the HR database user, which is included with the Oracle Database 10g Release 2 sample schemas and illustrates the minimum steps for producing a fully functional application that responds to a change notification event.

Code Listing 1: Database change notification code

static void Main(string[] args)
  string sql = "select first_name, last_name, salary from employees where employee_id = 149";
  string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
  OracleConnection con = new OracleConnection(constr);
  OracleCommand cmd = new OracleCommand(sql, con);
  OracleDependency dep = new OracleDependency(cmd);
  dep.OnChange += new OnChangeEventHandler(OnDatabaseNotification);
  while (notificationReceived == false)
    Console.WriteLine("Waiting for notification...");
  Console.WriteLine("Press ENTER to continue...");
public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)
  Console.WriteLine("Database Change Notification received!");
  DataTable changeDetails = args.Details;
  Console.WriteLine("Resource {0} has changed.", changeDetails.Rows[0]["ResourceName"]);
  notificationReceived = true;

For your application to utilize change notification, the application's database user must have the CHANGE NOTIFICATION database privilege. Before running the code in Listing 1, run the following statement, using a DBA connection in a tool such as SQL*Plus or Oracle Developer Tools for Visual Studio .NET, to ensure that the HR user can use the change notification feature:

grant change notification to hr;

Now that the HR user has the appropriate privilege to use the change notification feature, let's consider the important parts of the C# sample code in Listing 1.

The following code creates the dependency object:

OracleDependency dep = 
new OracleDependency(cmd);

The dependency object is used to register the query with the database. Note that the creation of the dependency object itself does not register the query; the command execution does.

The sample application receives the change notification as an event initiated by the database. The following code attaches the OnDatabaseNotification method in the sample code to the dependency object:

dep.OnChange += 
new OnChangeEventHandler(

The application will call the OnDatabaseNotification method when the ODP.NET client receives a change notification event.

The OnDatabaseNotification method in Listing 1 handles the notification event from the database. When a change notification event is received, this code outputs a message to the console to indicate that a notification message has been received, displays the name of the resource that has been changed, and sets the notificationReceived variable to true.

The following code checks the notificationReceived variable and loops while the value is false:

while (notificationReceived == false)
    "Waiting for notification...");

This simulates work taking place in a simple console window. While performing the loop, the code outputs a simple message to the console and sleeps for two seconds.

To test the sample code, create a new console application and add a reference to Oracle Data Provider for .NET to the project, by selecting Project -> Add Reference from the Microsoft Visual Studio menu bar and then choosing Oracle.DataAccess.dll from the Add Reference dialog box.

Next add the following using statements to the top of the code file, to include the namespaces used in the project:

using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;

Replace the system-generated Main method with this column's sample code. Add the OnDatabaseNotification method to the class after the Main method, and build the project. (The sample download for this article includes all the appropriate namespaces and assembly references.)

This sample application retrieves information about an employee in the EMPLOYEES table, registering the following query for a change notification:

select first_name, last_name, salary 
from employees
where employee_id = 149

Another user will update that employee's salary in the database, and the database will send a change notification to the sample application. The application responds to the change notification by printing a simple message.

After building the project, run the resulting executable in a console window. While the application is running, log in as the HR user in SQL*Plus or Oracle Developer Tools for Visual Studio .NET to update the EMPLOYEES table. You can use the following sample UPDATE statement, which is included in the source code download in the increase_salary.sql file:

update employees set salary = salary+10 
where employee_id = 149;

The output from your application should resemble the following:

Waiting for notification...
Waiting for notification...
Waiting for notification...
Waiting for notification...
Database Change Notification received!
Resource HR.EMPLOYEES has changed.

Implementation Guidelines

As with any other feature, properly using Database Change Notification can greatly benefit the performance and usability of your application. The following guidelines will help you best use Database Change Notification in your ODP.NET application:

  • Use Database Change Notification only with data that changes infrequently. The benefit of using Database Change Notification can be lost when the data changes frequently, such as with a stock ticker. A continuous stream of changes means the overhead of constant notification traffic and subsequent data updates. If the data changes almost as frequently as it is read, don't use change notification. Just requery the database whenever you do a read.

  • Limit the number of Database Change Notification registrations for a particular query. If hundreds of users are monitoring for changes on the same query, hundreds of notifications will be sent to the client tier when a data change occurs. Rather than have each client in a system register for notification, move the code into a middle-tier element to consolidate the number of registrations and the workload.

Next Time

This column introduced the basics of using database change notification in your ODP.NET applications. The next column will examine in greater detail the classes that support change notification and show you how to optimize the use of database change notification.

Next Steps

READ more about ODP.NET
Oracle Data Provider for .NET Developer's Guide

the sample application for this column
 Oracle Developer Tools for Visual Studio .NET

.NET Developer Center