Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Oracle Database Change Notification Not Working

Oracle Database Change Notification Not Working

Using Database Change Notification With ODP.NET and Oracle Database 11g

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial introduces the Database Change Notification feature supported by the Oracle Data Provider for .NET and Oracle database.

Time to Complete

Approximately 30 minutes

Overview

The Oracle Data Provider for .NET provides a notification framework that supports Database Change Notification in the database server. Database Change Notification enables applications to receive notifications from a registered callback routine when there is a change in a query result set, schema objects, or the state of the database that could affect the results of a cached client result set.

Using this feature in ODP.NET, an application can maintain the validity of the client-side cache (for example, the ADO.NET DataSet) easily. The client does not need to maintain a connection to the database. It will receive notifications even when no longer connected to the database.

This feature is intended for queries whose results change infrequently over time and for those who cannot maintain a large number of active database connections.

In this tutorial, an application will display a DataSet grid containing the values of the HR.EMPLOYEES table. After populating the grid, the application will disconnect from the database and await notification that data has changed. Once the change is detected, the application will reconnect to the database and refresh the data.

This tutorial demonstrates the flexibility of Database Change Notification feature, including persisting notification registrations even after numerous changes and distinguishing at the row level whether a change would affect the client's query results. This feature is called Database Change Notification (DCN) in Oracle Database 10g Release 2. In Oracle Database 11g, this feature was renamed to Continuous Query Notification (CQN). Both these names refer to the same feature.

Prerequisites

Before starting this tutorial, you should:

.

Install Microsoft Visual Studio 2010

.

Install Oracle Database 9.2 or later or Oracle Database XE

.

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.2.0.1.2 or later from OTN

.

Extract these files into your working directory.

Granting Privileges to HR

A user or role must have CHANGE NOTIFICATION privileges prior to being able to run applications that accept Change Notification callbacks.

.

From your windows program menu, start Visual Studio. In Visual Studio, from View menu select Server Explorer.

A Server Explorer panel opens.

.

Right click Data Connections and select Add Connection from the menu.

An Add Connection dialog box opens.

.

In the Add Connection dialog box, ensure the Data Source is set to Oracle Database Server (Oracle ODP.NET).

Note: If the Data Source is not set to Oracle Database Server (Oracle ODP.NET), click "Change" to select the appropriate data source and the data source provider.

In the Change Data Source dialog box, select Oracle Database Server from the Data Source list and select Oracle Data Provider for .NET from the Data Provider drop down list. Click OK.

.

In the Add Connection dialog box, make sure the Connection Details tab is selected. From the Data Source drop down list, select the Oracle Database instance SID you are using.

Enter user name as SYS, password as oracle and select the SYSDBA role from the dropdown list. Click OK to connect.

Note: Your sys password may not be oracle. Check with your database administrator for the sys password.

.

Similarly, create a connection for HR schema. Right click Data Connections and select Add Connection.

In the Add Connection dialog box, the appropriate data source and data source provider is already selected.

In the Connection Details tab, enter user name as HR and password as hr. Click OK.

Note: Your HR schema's password may not be hr. Check with your database administrator for the HR schema's password.

The Server Explorer shows the two connections.

.

Right click SYS.ORCL connection node in Server Explorer and select Query Window. This will open the Query Window.

.

In the Query Window, enter the following grant statement:

grant change notification to hr

Click the Execute Query button.

You get a successfully completed message in the query output window. Close the Query Editor.

Loading and Running the Change Notification Demo Application

A Change Notification Visual Studio solution has been precreated for you to load and run.

This program will select all records in the EMPLOYEES table in the HR schema where the EMPLOYEE_ID value is less than 200, then place the results in an ADO.NET DataSet. The DataSet does not maintain a connection to the database server, so it no longer holds onto any database resources, allowing for better scalability.

Before the SELECT statement is executed, an OracleDependency object is bound to an OracleCommand. When the SELECT statement is executed, this OracleDependency will create a notification registration on the database server.

Even though the original DataSet is disconnected, when another user modifies the EMPLOYEES table, the notification handler will inform the .NET application a change to the source data has occurred. On the client side, this will trigger an event handler that refreshes the DataSet results.

.

In Visual Studio, from the File menu, select Open > Project/Solution. In the Open project dialog box, navigate to the ChangeNotification file from the files downloaded in your working directory. Select DB Change Notification solution file. Click Open.

.

From View menu, select Solution Explorer. In the Solution Explorer, right click Form1.cs and select View Code.

The Form1.cs code window opens.

.

You may need to modify connection string attributes to connect to Oracle. You don't need to change the Data Source connection string attribute if your data source is a single local instance with the Service Name as orcl or Net Service Name as orcl.

If you need to use a different data source alias, modify the Data Source value in the connection string variable, "constr".

Note: You will connect to the HR schema, so check that User Id is set to HR and Password is set correctly. In the example code, HR's password is "hr". If this is not your HR schema's password, check with your DBA for the correct password.

.

Add the following code under their respective comments. The comments explain what each line of code does.

cmd.AddRowid = true;
OracleDependency dep = new OracleDependency(cmd);
cmd.Notification.IsNotifiedOnce = false;
dep.OnChange += new OnChangeEventHandler(OnMyNotificaton);

.

In the Visual Studio, from Debug menu, select Start Debugging. This will run the demo. If any break points are hit, press F5 to continue running until a grid containing the values of the HR. EMPLOYEES table appears.

Note: When you run this program for the first time, you may receive the message as shown in the screenshot below from Windows Firewall. Click on Unblock to allow the change notifications to be sent from the database server to the client.

Do not exit the running demo.

Modifying the Employees Table and Watching a Change Notification Event Occur

Now the Change Notification application is running and ready to receive notifications that data in the HR.EMPLOYEES table has been changed. Let's modify the EMPLOYEES table data.

.

Click the (+) plus sign next to the HR.ORCL node in Server Explorer to expand it. Expand the Tables node.

Right click the EMPLOYEES table and select Retrieve Data... to show it's data in Visual Studio.

You may get a Microsoft Visual Studio dialog box asking you confirm whether you want to retrieve more number of rows than the set limit of 100. Click Yes.

A window opens showing the employees table data.

.

In the newly opened Data Window in Visual Studio (NOT in the Change Notification application grid), modify one of the SALARY values. Right-click the Data tab and select Save to cause the value to be committed to Oracle Database.

.

In a second or two, the Change Notification application opens a Notification Alert window, informing that the result set has changed.

Note: See the taskbar at the bottom of the desktop if you don't see the window. It may be hidden. (If a breakpoint fires before seeing this window, press F5 to continue)

Click OK. Close the Oracle Database Change Notification Demo.

Adding a Callback Routine to Update Client Data After a Change Notification

Right now, our application receives the Change Notification callback, but our event handler contains nothing more than the dialog box you saw in the previous section. Let's add code to connect to the database and fetch the rows that have been modified.

For single table SELECTs, the Change Notification callback will return the ROWID information for all modified rows in the table. The ROWID will be appended to the original query so that you can retrieve only the rows that were changed, not the entire data set which would be less optimal. You will then refresh the DataSet with just the new data.

.

Click the Form1.cs tab. In the dropdown box select OnMyNotificaton(..

This will take you to the OnMyNotificaton callback routine.

.

Add following lines of code under their respective comments. The comments explain what each line of code does.

The following lines of code append ROWID(s) to the base query to retrieve just modified row(s).

DataRow detailRow = args.Details.Rows[0];
string rowid = detailRow["Rowid"].ToString();
string sqlUpdate = sqlSelect + "where rowid = \'"+ rowid +"\'";

Add the following lines of code to append on to the sqlUpdate statement if there are additional updated rows .

for (int i = 1; i < args.Details.Rows.Count; i++)

{

detailRow = args.Details.Rows[i];

rowid = detailRow["Rowid"].ToString();

sqlUpdate = sqlUpdate + " or rowid = \'"+ rowid +"\'";

}

.

Add following lines of code to refresh the data.

OracleConnection con2 = new OracleConnection(constr); OracleCommand cmd2 = new OracleCommand(sqlUpdate,con2);
con2.Open();
OracleDataAdapter da2 = new OracleDataAdapter(cmd2);
da2.Fill(ds, tablename);

.

From Build menu, select Build Solution.

Ensure there are no errors or warnings reported.

.

From Debug menu, select Start Debugging. A grid containing the values of the HR. EMPLOYEES table appears.

.

You will now change multiple rows in the database and see ODP.NET update only those rows that were changed.

In the Solution Explorer, double click multiple_row_change.sql.

.

Select the following code from the multiple_row_change.sql. Press CTRL+C to copy the code.

update employees set salary=salary+10 where employee_id=100 or employee_id=101; commit;
In Server Explorer, right-click HR.ORCL and select Query Window. In the Query Window, paste the code and make sure both the statements are selected. Click Execute Query to run the query.

.

You get the Notification Alert. Click OK.

Check the modified rows refreshed in the application grid.

Note: The Oracle database identifies the changed rows via their ROWIDs. The OracleNotificationEventArgs instance passes the ROWID information back to ODP.NET. Only the rows that have changed are retrieved, not the entire table. This saves the application from refreshing data that is still valid.

In addition, you will notice that for each distinct database change, you receive one notification. Even though two rows were modified, the modification occurred as one discrete event. As such, only one notification was delivered.

Close the Oracle Database Change Notification Demo window.

Using Result-Set-Change Notifications

Beginning with Oracle Database 11g, ODP.NET can now register its queries with the database and receive either object-change notifications or result-set-change notifications. Prior to 11g, only object type notifications were possible.

Object-change notifications result from DML or DDL changes to the objects (e.g. tables) associated with the queries. Result-set-change notifications (the default starting with 11g) result from DML or DDL changes to the specific result set rows associated with the queries.

Result-set-change notifications provide better granularity for client applications by focusing only on the specific result set selected, while object-change notification handlers are more lightweight.

Note: This part of the tutorial will only work properly if you are using ODP.NET 11g and Oracle Database 11g.

.

From the Visual Studio menu, select Debug > Start Debugging. The Oracle Database Change Notification demo windows shows the Employees records.

Now, you change multiple rows in the database. ODP.NET will update only those rows that were changed.

If not already open, from the Solution Explorer, open multiple_row_change.sql.

.

Select the following code from the multiple_row_change.sql. Press CTRL+C to copy the code.

Update employees set salary=salary+10 where employee_id=200 or employee_id=201;
commit; In Server Explorer, right-click HR.ORCL and select Query Window. In the Query Window, paste the code and make sure both the statements are selected. Click Execute Query to run the query.

When you have done that, you will notice that the Notification Alert window has not appeared.

That is because the update statement affects rows outside of the original query, so the database notification handler took no action.

Summary

In this tutorial, you have learned how to:

  • Grant privileges to HR for Change Notification
  • Load and run the Change Notification Demo Application
  • Modify the Employees table and watch a Change Notification event occur
  • Add a callback routine to handle the Change Notification Event
  • Use result-set change notifications
  • Lead Curriculum Developer: Anupama Mandya, Pallavi Rajan
  • Other Contributors: Marcie Young, Christian Shay

Oracle Database Change Notification Not Working

Source: https://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/228313.htm