Jdbc: oracle database change notification and duplicate events

Question

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

Problem: I get a lot of detection with one update in my table.

I think his oracle cache, etc.

Is it possible to detect only real changes?

my code is:

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); } } 

Conclusion:

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

Answer 1

The problem was that I repeatedly registered in the database. The solution was checked by the registered user for change events before registration.

I checked this with this query:

 select TABLE_NAME from USER_CHANGE_NOTIFICATION_REGS 

Answer 2

The fact is that according to https://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleConnection.html you are responsible for releasing your DatabaseChangeNotification by unregistering your connection. Otherwise, as described, "Registration will continue to live after this connection is closed. You need to explicitly cancel registration in order to destroy it on the server and free up resources in the driver."

Value, if you test your sample code and kill it, your registrations remain on the server, and you receive additional notifications after registering the next DatabaseChangeNotification parameter. Unfortunately, I have not yet discovered that it is hot in order to reconnect to live registration - for this I would need regid, but I still do not know how to get it from OracleConnection.

Answer 3

As already mentioned, you are responsible for releasing your DatabaseChangeNotification by canceling them when you connect. In my case, for some reason, I do this when the application server starts on tomcat. First I release old registrations, and then create new ones. I think I can reuse the old registration, but in any case I do not.

I am doing something like this:

This query returns existing registrations. You must log in as the same user who registered the notification.

 SELECT REGID, CALLBACK FROM USER_CHANGE_NOTIFICATION_REGS 

then a code like this pseudo code unregisters the notification.

connection.unregisterDatabaseChangeNotification (REGID, CALLBACK) is called for each row returned by the request.

Something else that I discovered is that when I update a row using PL / SQL Developer using the included editor, I get several notifications for the same update. When using sql to update a row, I get one notification as expected. I do not know why this is happening, but it is happening.

Answer 4

From what I have read so far, I am also a little confused.

Did you try to print the transaction to see if this is really a unique event?

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

The listeners I worked with in JMS require confirmation, but I don't see anything like this in Oracle docs ( http://docs.oracle.com/cd/B28359_01/java.111/b31224/dbmgmnt.htm ).

I also found that this could break the oracle cache if the event handling has not been fixed, but it looks like you are already doing this ...

Good luck

Answer 5

You will receive an event for each commit that changes one of the tables that you use in your query (in the code example, there is only one table called “EXAMPLE”). Think of it as a “TCN Table Change Notification”. In other words, you can get a lot of false positives because you are only interested in one line, but you will be notified if other lines are changed. Then you need to filter events. That is why this function should be used only for reading mostly tables.

In 11gR2, Oracle improved this notification mechanism to receive a more subtle notification called "Request Change Notification." This time you will only be notified of changes that affect the results of your request. There is an option that must be enabled to enable QCN instead of TCN. Note that the server may not always enable QCN. If the request is too complex, it will return to the TCN.

Answer 6

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 7

Typo error correction:

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()); }