Oracle Database Change Notification JDBC Style Example


this code example test in oracle 11g R2 and run on user scott, first we must grant privilage for notification

grant change notification to scott;

this grant can use scott account grant to it self.

project structure need only one file (OracleDCN.java) and JDBC library

OracleDCN.java

package com.example.dcn;

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

when something change in EXAMPLE table event will triggle and print ROW_ID

after database change notification has registed we can check using query

select * from USER_CHANGE_NOTIFICATION_REGS;

Download Source From : Github

Ref : Database Change Notification



me on google plus+Jirawong Wongdokpuang

Advertisements
Oracle Database Change Notification JDBC Style Example

5 thoughts on “Oracle Database Change Notification JDBC Style Example

  1. Sudip says:

    Same codebase is not working for custom user.
    e.g. I had to create a new user (schema) for my Oracle database.

    create user test identified by test
    default tablespace users
    temporary tablespace temp
    quota unlimited on users;

    grant connect, resource to test;

    grant change notification to test;

    Replaced scott/tiger in above code base with test/test, however, I am not getting any notification for insert/update/delete etc. Notification is received only during database startup/shutdown.

  2. nice example. Big tnx.
    But i get many detection by single update on my table.
    for example:
    Changed row id : AAAFSzAAAAAAAG8AAA
    Changed row id : AAAFSzAAAAAAAG8AAA
    Changed row id : AAAFSzAAAAAAAG8AAA
    Changed row id : AAAFSzAAAAAAAG8AAA

    I think its oracle cache etc.

    Is it possible only real change to detect?

    tnx

    1. multiple notification will notify when register multiple time on same table,

      =====================================
      select * from USER_CHANGE_NOTIFICATION_REGS;
      =====================================

      will show u how many notification ur register .
      the Oracle document’s show

      the registration survives until explicitly unregistered by the client application or timed-out or implicitly removed by the database for some other reason (such as loss of privileges).

      link : http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dcn.htm#BGBFDCHA

      when not want to wait timed-out, u can revoke privilage by

      =====================================
      revoke change notification to scott;
      =====================================

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s