Unregister oracle change notification

Question

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

Answer 1

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