CODEWITHSUNDEEP
javaoracle-databasejdbc
basin
basin

Reputation: 4200

persistent Database Change Notifications

I need to monitor an oracle table for changes and I'm trying to choose between Database Change Notifications and Advanced Queuing.

I don't understand certain points in Database JDBC Developer's Guide and getDatabaseChangeRegistration javadoc

If I register a DB change registration (DCR) with the NTF_QOS_RELIABLE flag, I expect the notifications to persist while my jdbc application is down. However, I don't see a way to restore existing DCR after my app restarts: according to javadoc, getDatabaseChangeRegistration() is only for PLSQL listeners. And it seems that jdbc DCRs are destroyed when my app dies and I don't even have to unregister them.

After my program restarts I sometimes get notifications with a previous registration id. It is not necessary to call stmt.setDatabaseChangeRegistration() every time I start my app.

I never receive changes that happened while my app was down and this is the biggest problem. What does NTF_QOS_RELIABLE do then?

package org.foo;

import static oracle.jdbc.OracleConnection.*;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Locale;
import java.util.Properties;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

// CHECKSTYLE.OFF: Name|Reg
public final class TestDbListener {

    private TestDbListener() {}

    public static void main(final String[] args) throws Exception {
        Locale.setDefault(Locale.US);

        Class.forName("oracle.jdbc.OracleDriver");
        final OracleConnection conn =
                (OracleConnection) DriverManager.getConnection(
                        "jdbc:oracle:thin:@192.168.56.150:1521:xe", "scott", "tiger");

        final Properties props = new Properties();
        props.setProperty(NTF_QOS_RELIABLE, "true");

        final DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(props);
        final DCNListener list = new DCNListener();
        dcr.addListener(list);

        if (true) {

            // now you need to add whatever tables you want to monitor
            final OracleStatement stmt = (OracleStatement) conn.createStatement();
            // associate the statement with the registration:
            stmt.setDatabaseChangeRegistration(dcr);

            final String sql = "select * from a where 1=2";
            final ResultSet rs = stmt.executeQuery(sql);

        }

        final String[] tableNames = dcr.getTables();
        for (int i = 0; i < tableNames.length; i++) {
            System.out.println(tableNames[i] + " has been registered.");
        }

        Thread.sleep(1000000);

        // rs.close();

    }

}

class DCNListener implements DatabaseChangeListener {

    @Override
    public void onDatabaseChangeNotification(final DatabaseChangeEvent event) {
        System.out.println("onDatabaseChangeNotification: " + event);
    }
}

SQL:

-- sysdba: 
-- grant change notification to scott;

-- scott:
create table a ( a int );

insert into a values ( 1 );
commit;

Upvotes: 5

Views: 1439

Answers (1)

Jean de Lavarene
Jean de Lavarene

Reputation: 3783

NTF_QOS_RELIABLE can be used to control which type of queue is used on the server to handle the notifications. See this doc: https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci10new.htm#CHDFCCJE

About NTF_QOS_RELIABLE:

Surviving instances of Oracle RAC can be used to send and retrieve continuous query notification messages, even after a node failure because invalidations associated with this registration are queued persistently into the database. If FALSE, then invalidations are enqueued into a fast in-memory queue. Note that this option describes the persistence of notifications and not the persistence of registrations. Registrations are automatically persistent by default.

If you app dies, the assumption is that during restart you'll fetch the latest data from the table you want to monitor. So the notifications that were sent between the crash and the restart are not needed.

Note that upon restart it's required to call conn.registerDatabaseChangeNotification to restart the driver's listener.

Upvotes: 1

Related Questions