Determining the DatabaseChangeEvent which occurred on a TABLE

Not for myself:

Registration Properties

Properties props = new Properties();
props.put(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
props.put(OracleConnection.NTF_QOS_RELIABLE, "false");
props.setProperty(OracleConnection.DCN_BEST_EFFORT, "true");

DatabaseChangeRegistration dcr =
    conn.registerDatabaseChangeNotification(props);

Listener Class

package pas.jdbc;

import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.RowChangeDescription;
import oracle.jdbc.dcn.RowChangeDescription.RowOperation;
import oracle.jdbc.dcn.TableChangeDescription;


public class DCNListener implements DatabaseChangeListener
{
DCNDemo demo;
DCNListener(DCNDemo dem)
{
  demo = dem;
}

public void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent)
{
System.out.println("DCNListener: got an event (" + this + ")");
System.out.println(databaseChangeEvent.toString());
TableChangeDescription [] tableChanges =
      databaseChangeEvent.getTableChangeDescription();

for (TableChangeDescription tableChange : tableChanges)
{
  RowChangeDescription[] rcds = tableChange.getRowChangeDescription();
  for (RowChangeDescription rcd : rcds)
  {
    System.out.println("Affected row -> " + rcd.getRowid().stringValue());
    RowOperation ro = rcd.getRowOperation();
    if (ro.equals(RowOperation.INSERT))
    {
      System.out.println("INSERT occurred");
    }
    else if (ro.equals(RowOperation.UPDATE))
    {
      System.out.println("UPDATE occurred");
    }
    else if (ro.equals(RowOperation.DELETE))
    {
      System.out.println("DELETE occurred");
    }
    else
    {
      System.out.println("Not a INSERT/DELETE/UPDATE change");
    }
  }
}

synchronized( demo )
{
  demo.notify();
}

}

Closing Obsolete Database Change Notification Registrations

When enabling Database Change Notification (DCN) a registration is created at the database level which requires it to closed when no longer required. Here is how to close an obsolete registration from JDBC in a case where you don't have a valid DatabaseChangeRegistration object (for example a JVM crash).

1. Find the obsolete registration as shown below.

SCOTT@linux11g> select regid, CALLBACK, table_name from USER_CHANGE_NOTIFICATION_REGS;

REGID CALLBACK                                 TABLE_NAME
---------- ---------------------------------------- --------------------
 100 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.1 SCOTT.DEPT
     87.80.135)(PORT=47632))?PR=0

2. Use JDBC code as follows to delete the obsolete registration.

package pas.jdbc;

import java.sql.SQLException;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

public class DeregisterDCN
{
  private OracleDataSource ods = null;
  public final String userId = "scott";
  public final String password = "tiger";
  public final String url = "jdbc:oracle:thin:@beast.au.oracle.com:1521:linux11g";
  private int REGID = 100;
  
  public DeregisterDCN() throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(userId);
    ods.setPassword(password);
    ods.setURL(url);
  }

  public void run () throws SQLException
  {
    try
    {
      OracleConnection conn = getConnection();
      conn.unregisterDatabaseChangeNotification(REGID);
      System.out.println("unregistered with id " + REGID);
    }
    catch (SQLException sqle)
    {
      System.out.println("Error trying to delete registration with id " + REGID);
      sqle.printStackTrace();
    }
  }
  
  public OracleConnection getConnection() throws SQLException
  {
    OracleConnection conn = (OracleConnection)ods.getConnection();
   
    return conn;
  }
  
  public static void main(String[] args) throws SQLException
  {
    DeregisterDCN deregisterDCN = new DeregisterDCN();
    deregisterDCN.run();
  }
}

Few things to note though..

1. The database user has been granted the following as user SYS to enable DCN.

> grant change notification to scott;

> grant execute on DBMS_CQ_NOTIFICATION to scott;

2. The method to use is OracleConnection.unregisterDatabaseChangeNotification(int). That requires you to cast a JDBC Connection to an OracleConnection to use that method as shown below.

OracleConnection conn = (OracleConnection)ods.getConnection();


3. They may be more then one registration so make sure you determine the correct entry to remove. If not you may end up deleting a registration which is currently active.