jdbc: oracle database change notification & duplic

Question

I need some Listener for any change(update, insert, delete) of Oracle database table.

Problem: I get many detection by single update on my table.

I think its oracle cache etc.

Is it possible only real changes to detect?

my code:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

public class OracleDCN {
    static final String USERNAME = "scott";
    static final String PASSWORD = "tiger";
    static String URL = "jdbc:oracle:thin:@localhost:1521:stingdev";

    public static void main(String[] args) {
        OracleDCN oracleDCN = new OracleDCN();
        try {
            oracleDCN.run();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void run() throws Exception{
        OracleConnection conn = connect();
        Properties prop = new Properties();
        prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
        DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);

        try{
            dcr.addListener(new DatabaseChangeListener() {

                public void onDatabaseChangeNotification(DatabaseChangeEvent dce) {
                    System.out.println("Changed row id : "+dce.getTableChangeDescription()[0].getRowChangeDescription()[0].getRowid().stringValue());
                }
            });

            Statement stmt = conn.createStatement();
            ((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
            ResultSet rs = stmt.executeQuery("select * from EXAMPLE where ID=1");
            while (rs.next()) {
            }
            rs.close();
            stmt.close();
        }catch(SQLException ex){
            if (conn != null)
            {
                conn.unregisterDatabaseChangeNotification(dcr);
                conn.close();
            }
            throw ex;
        }
    }

    OracleConnection connect() throws SQLException {
        OracleDriver dr = new OracleDriver();
        Properties prop = new Properties();
        prop.setProperty("user", OracleDCN.USERNAME);
        prop.setProperty("password", OracleDCN.PASSWORD);
        return (OracleConnection) dr.connect(OracleDCN.URL, prop);
    }
}

Output:

Changed row id : AAAFSzAAAAAAAG8AAA
Changed row id : AAAFSzAAAAAAAG8AAA
Changed row id : AAAFSzAAAAAAAG8AAA
Changed row id : AAAFSzAAAAAAAG8AAA

Answer 1

Correcting typo error:

Please modify the event handler as follows:

public void onDatabaseChangeNotification(DatabaseChangeEvent dce) {
   if (dce.getRegId() == dcr.getRegId()) 
                    System.out.println("Changed row id : "+dce.getTableChangeDescription()[0].getRowChangeDescription()[0].getRowid().stringValue());
                }

Answer 2

Problem was I registered to database many times. The solution was check registered user for change events before register.

I checked that with this query:

select TABLE_NAME 
from USER_CHANGE_NOTIFICATION_REGS

Answer 3

You will get an event for each commit that modifies one of the tables you're using in your query (in your code sample only one table called "EXAMPLE"). Think of it as "Table Change Notification" TCN. In other words you may get a lot of false positives because you're only interested in one row but you'll be notified if other rows are changed. It's then up to you to filter the events. This is why this feature should only be used for read mostly tables.

In 11gR2, Oracle improved this notification mechanism to allow a finer notification called "Query Change Notification". This time you will only be notified by changes that affect your query results. There is an option that needs to be turned on to enable QCN instead of TCN. Note that the server may not always be able to enable QCN. If the query is too complex it will fall back to TCN.

Answer 4

Please modify the event handler as follows:

public void onDatabaseChangeNotification(DatabaseChangeEvent dce) {
    if (e.getRegId() == dcr.getRegId()) 
        System.out.println("Changed row id : "+dce.getTableChangeDescription()[0].getRowChangeDescription()[0].getRowid().stringValue());
}

Answer 5

As already said you are responsible for releasing your DatabaseChangeNotification by unregistering them at your connection. In my case for some reason I am doing this when application server starts on tomcat. First I am releasing old registrations and then create new. I think I can reuse the old registration but anyway i am not.

I am doing something like this:

This query returns existing registrations. You must login as the same user that registered the notification.

SELECT REGID, CALLBACK FROM USER_CHANGE_NOTIFICATION_REGS

then code like this pseudo code unregisters the notification.

connection.unregisterDatabaseChangeNotification(REGID, CALLBACK) this is called for every row returned by the query.

Something else I found is that when updating a row using PL/SQL Developer using the included editor I receive multiple notifications for the same update. When using sql to update the row I receive one notification as expected. I don't know why is this happening but it is happening.

Answer 6

From what I've read so far, I'm a bit confused as well.

Have you tried printing out the transactionId to see if it is, in fact, a unique event being generated?

System.out.println("DCE : regId="+dce.getRegristrationId()+"; transactionId="+dce.getTransactionId());

The listeners that I've been working with for JMS require an acknowledgment, but I don't see anything of that sort in the Oracle docs (http://docs.oracle.com/cd/B28359_01/java.111/b31224/dbmgmnt.htm).

I also found that it might disturb the oracle cache if the event treatment is unthreaded, but it looks you're already doing that too...

Good luck!