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 ( and JDBC library

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 {
        } catch (Exception ex) {
    private void run() throws Exception{
        OracleConnection conn = connect();
        Properties prop = new Properties();
        prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
        DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);
            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 ( {
        }catch(SQLException ex){
            if (conn != null)
            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


Download Source From : Github

Ref : Database Change Notification

me on google plus+Jirawong Wongdokpuang


5 thoughts on “Oracle Database Change Notification JDBC Style Example

  1. 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?


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


      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 :

      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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s