TOraChangeNotification Component

TOraChangeNotification component is used to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.

When the database issues change notification, it can contain some or all of the following information:

- Names of the modified objects. For example, the notification can specify that the hr.employees table was changed.

- The type of change. For example, the message specifies whether the change was caused by an INSERT, UPDATE, DELETE, ALTER TABLE, or DROP TABLE.

- The ROWIDs of the changed rows and the type of DML that changed them.

- Global events such as STARTUP and SHUTDOWN (consistent only). In a Real Applications Cluster, the database delivers a notification when the first instance on the database starts or the last instance shuts down.

The notification contains only metadata about the changed rows or objects rather than the changed data itself. For example, the database does not notify the client that monthly salary increased from 5000 to 6000. To obtain more recent values for the changed objects or rows, the client must query the database based on the information contained in the notification.

Database Change Notification is useful for an application that caches query result sets on mostly read-only objects in the mid-tier to avoid network round trips to the database. Such application can create a registration on the queries it is interested in caching using the change notification service. On changes to objects referenced inside those queries, the database publishes a change notification when the underlying transaction commits. In response to the notification, the application can refresh its cache by re-executing the queries.

TOraChangeNotification component represents dependency between an application and an Oracle database based on the database events in which the application is interested. To create subscription place on the form TOraChangeNotification component and assign it to ChangeNotification properties of datasets. When you open a dataset subscription on changes to database tables that dataset selects data from will be created. All datasets that use one TOraChangeNotification component share one change notification subscription. A dataset is registered in subscription when you open this dataset.

Components derived from TOraDataSet can automatically refresh their data in response to change notification. To enable autorefresh set ReflectChangeNotify in TOraDataSet.Options to True. Dataset refreshes when data in one of the tables that appear in query FROM clause is changed. If dataset's SQL has ROWID in SELECT clause and changed table corresponds UpdatingTable property of TOraDataSet then only changed rows are refreshed with RefreshRecord method. If TOraDataSet.SQLRefresh property is set it must use ROWID. Otherwise refreshing may be incorrect. If TOraDataSet.SQL property doesn't contain ROWID in SELECT clause or data is changed not in UpdatingTable but in other table that appears in query FROM clause the dataset performs full refresh with Refresh method.

Change notification can be handled manually using OnChange event of TOraChangeNotification component. For example:

procedure TForm1.OraChangeNotificationChange(Sender: TObject;
  NotifyType: TChangeNotifyEventType; TableChanges: TNotifyTableChanges);
var
  i, j: integer;
begin
  if NotifyType <> cneObjChange then
    Exit;

  for i := 0 to TableChanges.Count - 1 do begin
    Memo.Lines.Add(TableChanges[i].TableName);

    if cnoAllRows in TableChanges[i].Operations then
      Continue;

    for j := 0 to TableChanges[i].RowChanges.Count - 1 do
      Memo.Lines.Add(TableChanges[i].RowChanges[j].RowId);
  end;
end;

For the best performance of change notification, the following guidelines are presented. Registered objects are few and mostly read-only and modifications to these objects are rather an exception than a rule. If the object is extremely volatile, then it will cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large numbers of notifications, the OLTP throughput can be slowed down due to the overhead of the notifications generation.It is also a good idea to keep the number of duplicate registrations on any given object low (ideally one) in order to avoid the same notification message being replicated to multiple recipients.

TOraChangeNotification Component

See Also