jdbc: oracle database change notification & duplicate events

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

Only Registration IDs and transactions ids of events are different. Rests are same. — tsogtgerel.ts
– tsogtgerel.ts, Commented Feb 11, 2014 at 12:02

marc_s · Accepted Answer · 2014-06-20 15:44:16Z

4

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

edited Jun 20, 2014 at 15:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 27, 2014 at 7:18

tsogtgerel.ts

9752 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Argyriou Over a year ago

Where in your code do you register multiple times? I don't see anything like this.

tsogtgerel.ts Over a year ago

Not in this code register multiple times, just if i run this program multiple times.

Ahmed Ashour · Accepted Answer · 2017-11-20 15:11:55Z

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.

Włodar · Accepted Answer · 2015-10-19 14:34:40Z

1

The point 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 unregister them at your Connection. Otherwise as described "The registration will continue to live after this connection is closed. You need to explicitly unregister it to destroy it in the server and release the resources in the driver."

Meaning if you test your sample code and you kill it, your registrations remains living on the server and you receive extra notifications after registering next DatabaseChangeNotification. Unfortunately I haven't found out yet hot to reconnect with living registration - I would need regId for that, but I have no clue yet how to obtain it from the OracleConnection.

answered Oct 19, 2015 at 14:34

Włodar

313 bronze badges

1 Comment

Włodar Over a year ago

The DatabaseChangeEvent you receive from the subscription contains RegistrationId information, so you can match it with your current registration (received from DatabaseChangeRegistration.getRegId()).

blackstrype · Accepted Answer · 2014-02-11 10:31:29Z

0

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!

answered Feb 11, 2014 at 10:31

blackstrype

49110 silver badges17 bronze badges

3 Comments

tsogtgerel.ts Over a year ago

DCE : regId=81; transactionId=[B@7367cb55 DCE : regId=80; transactionId=[B@3b0a0018 DCE : regId=79; transactionId=[B@74439682 DCE : regId=78; transactionId=[B@43b12dbb DCE : regId=77; transactionId=[B@5161dc26 DCE : regId=76; transactionId=[B@3b794c60

blackstrype Over a year ago

Okay... so the listener is registered four separate times? And the transactionId indicates four separate transactions.

blackstrype Over a year ago

Maybe try printing Thread.currentThread().getId(); to see if the Java code is being executed in multiple separate threads.

Jean de Lavarene · Accepted Answer · 2015-10-20 13:29:09Z

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.

Ahmed Ashour · Accepted Answer · 2017-11-20 15:12:38Z

0

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

edited Nov 20, 2017 at 15:12

Ahmed Ashour

5,66310 gold badges41 silver badges63 bronze badges

answered Feb 10, 2016 at 5:20

Priyobroto Ghosh

12 bronze badges

1 Comment

ckruczek Over a year ago

Please don't post a new answer but edit your old post.

ckruczek · Accepted Answer · 2016-02-10 05:44:25Z

-1

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

edited Feb 10, 2016 at 5:44

ckruczek

2,4202 gold badges22 silver badges23 bronze badges

answered Feb 10, 2016 at 5:15

Priyobroto Ghosh

12 bronze badges

1 Comment

ckruczek Over a year ago

Please don't do these kind of multi-indirektional object access. This is very error-prone.

Collectives™ on Stack Overflow

jdbc: oracle database change notification & duplicate events

7 Answers 7

2 Comments

Comments

1 Comment

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

1 Comment

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related