Oracle Continuous Query Notification is a technology that allows an application to register queries with Oracle for notifications about changes in the result data. A query can be registered either for object change notification (OCN) or for query result change notification (QRCN).

Object change notification allows receiving notifications whenever a transaction changes an object, referenced by the query and commits. It is available in Oracle 10.2.0.2 and higher.

Query result change notification allows receiving notifications whenever a transaction changes the result of the query and commits. It is available in Oracle 11g and higher.

This technology is useful, for example, when a web application caches query results in order to avoid roundtrips to a database, and the queried data is not modified very frequently. Such an application may register these queries and refresh data from the database only when it receives notifications about data changes in the database.

In order to use the Continuous Query Notification feature, you need the following privileges in the Oracle database:

• EXECUTE privilege on the DBMS_CQ_NOTIFICATION package, whose subprograms you use to create a registration
• CHANGE NOTIFICATION system privilege
• SELECT privileges on all objects to be registered

Oracle Continuous Query Notification Support Implementation

Oracle Continuous Query Notification support in dotConnect for Oracle is implemented in the OracleDependency and OracleNotificationRequest classes and corresponding helper enums, delegates, etc. Both OCN and QRCN are supported. This feature is not supported in the Direct mode.

To register a query for change notification, you need to create an OracleDependency instance and add the corresponding OracleCommand with the query to it. OracleDependency will initialize the command's Notification property, with a new OracleNotificationRequest object. Set its properties to configure the notification registration and run the command with the query.

When you run this command, the OracleDependency object registers the notification and starts listening for messages from Oracle server on a port specified by the Port property. Every time a notification is received the OracleDependency fires the OracleDependency.OnChange event, which you can use to perform actions after receiving a notification.

You may register more OracleCommands objects for change notification using the AddCommandDependency method of the OracleDependency object.

This procedure is more or less common for both OCN and QRCN, but configuring of OracleDependency and OracleNotificationRequest is different for these object as well as arguments passed to the OnChange event may differ.

Object Change Notification

To use object change notification, you need to set the QueryBasedNotification property of the created OracleDependency object to false.

You may specify the database events, to get notifications for using the Operations property of OracleNotificationRequest and configure whether to send a notification only once, or be notified about all the changes till the Timeout expire by setting the IsNotifiedOnce property. Additionally, you may configure the notification message persistence in the database, so that it could not be lost upon database failures or shutdowns until delivery using the IsPersistent property.

Note that after you run the command, and the notification request is registered, modifying the NotificationRequest settings has no effect.

Here is an example of registering an OracleDependency instance for a simple SELECT command, modifying the resultset and processing the received change notification.

static void Main(string[] args)
{
        // Open the connection
        OracleConnection connection = new OracleConnection
                ("Server = Ora; User Id = Scott; Password = tiger;");
        connection.Open();
            
        // Create the Select command retrieving all data from the Dept table.
        OracleCommand selectCommand = new OracleCommand("select * from dept", connection);

        // Create an OracleDependency object and set it to track the result set returned by selectCommand.
        OracleDependency dependency = new OracleDependency(selectCommand);
        
        // Setting object-based change notification registration
        dependency.QueryBasedNotification = false;

        // When the IsNotifiedOnce property is true, only the first change 
        // of the traced result set will generate a notification.
        // Otherwise, notifications will be sent on each change 
        // during the selectCommand.Notification.Timeout period.
        selectCommand.Notification.IsNotifiedOnce = true;
        // Specifies whether notifications will contain information on rows changed.
        selectCommand.Notification.RowLevelDetails = true;

        // Set the event handler to the OnChange event.
        dependency.OnChange += new OnChangeEventHandler(OnDeptChange);
                        
        // When the select command is executed at the first time, a notification 
        // on changes of the corresponding result set is registered on the server.
        selectCommand.ExecuteReader();

        // Set and execute an insert command. The Dept table data will be changed, 
        // and a notification will be sent, causing the OnChange event of the 'dependency' object.
        OracleCommand insertCommand = new OracleCommand
                ("insert into dept values (100, 'New department', 'Some location')", connection);
        insertCommand.ExecuteNonQuery();

        // Pause the current thread to process the event.
        Thread.Sleep(10000);

}

// A simple event handler to handle the OnChange event.
// Prints the change notification details.
static void OnDeptChange(Object sender, OracleNotificationEventArgs args)
{
        DataTable dt = args.Details;

        Console.WriteLine("The following database objects were changed:");
        foreach (string resource in args.ResourceNames)
                Console.WriteLine(resource);

        Console.WriteLine("\n Details:");
        Console.Write(new string('*', 80));
        for (int rows = 0; rows < dt.Rows.Count; rows++)
        {
                Console.WriteLine("Resource name: " + dt.Rows[rows].ItemArray[0]);
                string type = Enum.GetName(typeof(OracleNotificationInfo), dt.Rows[rows].ItemArray[1]);
                Console.WriteLine("Change type: " + type);
                Console.Write(new string('*', 80));
        }
}

Sub Main(ByVal args() As String)
	' Open the connection
	Dim connection = New OracleConnection( _
		"Server = Ora; User Id = Scott; Password = tiger;")
	connection.Open()

	' Create the Select command retrieving all data from the Dept table.
	Dim selectCommand = New OracleCommand("select * from dept", connection)

	' Create an OracleDependency object and set it to track the result set returned by selectCommand.
	Dim dependency As OracleDependency = New OracleDependency(selectCommand)
	
	' Setting object-based change notification registration
	dependency.QueryBasedNotification = False

	' When the IsNotifiedOnce property is true, only the first change 
	' of the traced result set will generate a notification.
	' Otherwise, notifications will be sent on each change 
	' during the selectCommand.Notification.Timeout period.
	selectCommand.Notification.IsNotifiedOnce = True
	' Specifies whether notifications will contain information on rows changed.
	selectCommand.Notification.RowLevelDetails = True

	' Set the event handler to the OnChange event.
	AddHandler dependency.OnChange, AddressOf OnDeptChange

	' When the select command is executed at the first time, a notification 
	' on changes of the corresponding result set is registered on the server.
	selectCommand.ExecuteReader()

	' Set and execute an insert command. The Dept table data will be changed, 
	' and a notification will be sent, causing the OnChange event of the 'dependency' object.
	Dim insertCommand = New OracleCommand( _
		"insert into dept values (100, 'New department', 'Some location')", connection)
	insertCommand.ExecuteNonQuery()

	' Pause the current thread to process the event.
	Thread.Sleep(10000)

End Sub


' A simple event handler to handle the OnChange event.
' Prints the change notification details.
Sub OnDeptChange(ByVal sender As Object, ByVal args As OracleNotificationEventArgs)
	Dim dt As DataTable = args.Details

	Console.WriteLine("The following database objects were changed:")
	For Each resource As String In args.ResourceNames
		Console.WriteLine(resource)
	Next

	Console.WriteLine(vbCrLf + "Details: ")
	Console.Write(New String("*", 80))

	For rows As Integer = 0 To dt.Rows.Count - 1
		Console.WriteLine("Resource name: " + dt.Rows(rows).ItemArray(0))
	Dim type As String = [Enum].GetName( _
		GetType(OracleNotificationInfo), dt.Rows(rows).ItemArray(1))
	Console.WriteLine("Change type: " + type)
	Console.Write(New String("*", 80))
	Next
End Sub

Query Result Change Notification

All the above mentioned configuration properties are also applicable to QRCN. For QRCN, you may also configure the notification mode via the QueryBasedNotificationMode property. You may either use the BestEffort mode, which means that the application is notified about all the resultset updates, but may also produce false positive notifications, or the Guaranteed mode, which sends a notification only if the query result data were indeed changed in the database. Note that the Guaranteed mode has stricter requirements for a query to register, and some queries that can be registered for notifications in the BestEffort mode, cannot be registered in the Guaranteed mode. See more details about requirements to queries in Oracle documentation.

Additionally, for QRCN, you may configure notification grouping. Notification grouping means that notifications are not set immediately after each event that influences the query result data, but they are sent at most once per GroupingInterval (specified in seconds). You may also set GroupingType, which determines whether to unite all the notifications within the interval into one Summary notification or send only the notification about the Last event happened during the interval. To enable grouping, set the GroupingNotificationEnabled property to true.

As well as for OCN, after you run the command, and the notification request is registered, modifying the NotificationRequest settings has no effect.

For query result change notification, the OnChange event provides additional information that is not available for object change notification. The Details table also provides the QueryId column, which allows you to determine, for which query this notification is sent. You can determine the correspondence between query ids and registered queries using the RegisteredQueryIds and RegisteredQueryResources properties of the OracleDependency object, which store respectively the lists of registered query ids and registered commands. Note that the latter properties are appropriately updated when the registered OracleCommand object executes the query, not when it is added to the OracleDependency object.

See Also

OracleAlerter class OracleDependency class