3

this topic is related to one from Java but i cant find solution for C#. http://theblasfrompas.blogspot.com/2010/01/closing-obsolete-database-change.html

I am using Oracle.ManagedDataAccess.dll with Change Notification.

All works fine but I have one problem. When my application starts I create Database Notification (with Timeout 0 - it must be) and i have handle to OracleDependency. When my application is stopped I can use this handle to call remove registration in this way:

oracleDependency.RemoveRegistration(connection);

The problem appears when my application crashes in some way and i am unable to call RemoveRegistration method. I lose handle to OracleDependency so after restart application I cant remove obsolete registrations. As always on start application will create new registration but now will exists TWO - one new and one obsolete. In this way my application will get two times notification. The question is - how to remove obsolete notifications created by my application.

Ok my further investigation is below: I found on oracle docs that exists static method OracleDependency.GetOracleDependency(string guid) So after I create oracle dependency I save his Id (seems its guid). When my app is stopped i can use this method to get my dependency. Unfortunately it didnt work after application restart:/ If i try to get OracleDependency by this Id it return null but it strill exists in USER_CHANGE_NOTIFICATION_REGS

templaris
  • 221
  • 5
  • 11

3 Answers3

2

Java implementation to remove all change notification registrations from the database 

Statement stmt= conn.createStatement();
ResultSet rs = stmt.executeQuery("select regid,callback from USER_CHANGE_NOTIFICATION_REGS");
while(rs.next())
{
  long regid = rs.getLong(1);
  String callback = rs.getString(2);
  ((OracleConnection)conn).unregisterDatabaseChangeNotification(regid,callback);
}
rs.close();
stmt.close();

You need to have ojdbc6/7.jar in class path to execute this code.

Original post:https://community.oracle.com/message/9315024#9315024

TMtech
  • 1,076
  • 10
  • 14
2

Although this is a rather old question I will describe my experience with Oracle CQN just in case it helps someone. The feature works better with java where its easy not only to register but also to unregister the notification. In .NET if the application crashes there is no way in my experience to unregister the notification with code. Revoking change notification is not working immediately. Until database restart the registration survived the revoke. It seems that Oracle removes the registration when there is a problem in communication with the notification receiver. I was able to unregister notifications using this behavior. By turning on the firewall for example! Another solution I use to unregister the notifications for a particular oracle user is a tool I wrote in java named NotificationRegistrationsCleaner.jar. It can be downloaded from the following link. We call it passing 4 parameters it like this.

java -jar NotificationRegistrationsCleaner.jar [oracle ip] [oracle service] [oracle user] [oracle password]

The tool displays the removed registrations. Far from perfect but its doing the job. The java code is very similar to @TMtech code described above.

NotificationRegistrationsCleaner.jar

Panagiotis Piperopoulos
  • 394
  • 3
  • 8
0

You just can revoke change notification from current user and grant it again. I know, this isn't best solution, but it work.

user1629203
  • 1