Database Change Notification Not Working in Java

Question

I am trying to get Database Change Notification. I am currently using JDK 11 , ojdbc10 jar and Oracle 19c Enterprise Edition.

Below are the steps I have followed :

grant change notification to <user>;

CREATE TABLE myuser.Test (TEST VARCHAR2(255));

GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE TO myuser.test;GRANT CHANGE NOTIFICATION TO myuser.test;GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO myuser.test;Below is the Java Code

import java.sql.*;
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 Main  {
   public static void main(String[] args) {
       try {
           // Connect to the database
           Class.forName("oracle.jdbc.driver.OracleDriver");
           //jdbc:oracle:thin:@//HOSTNAME:PORT/SERVICENAME
           OracleConnection conn = (OracleConnection) DriverManager.getConnection("jdbc:oracle:thin:@//<serverIP>:1521/orcl7", "username", "password");


           Properties prop = new Properties();
           prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");
           prop.setProperty(OracleConnection.DCN_BEST_EFFORT,"true");
           prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");
           DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);

           try {
               // add the listenerr:
               DCNDemoListener list = new DCNDemoListener();
               dcr.addListener(list);

               // second step: add objects in the registration:
               Statement stmt = conn.createStatement();
               // associate the statement with the registration:
               ((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
               ResultSet rs = stmt.executeQuery("select * from myuser.test where test='TEST'");
               
               while (rs.next()) {
               }
               String[] tableNames = dcr.getTables();
               for (int i = 0; i < tableNames.length; i++)
                   System.out.println(tableNames[i] + " is part of the registration.");
               rs.close();
               stmt.close();
           } catch (SQLException ex) {
               // if an exception occurs, we need to close the registration in order
               // to interrupt the thread otherwise it will be hanging around.
               if (conn != null)
                   conn.unregisterDatabaseChangeNotification(dcr);
               throw ex;
           } finally {
               try {
                   // Note that we close the connection!
                  // conn.unregisterDatabaseChangeNotification(dcr);
                   conn.close();
               } catch (Exception innerex) {
                   innerex.printStackTrace();
               }
           }



       } catch (SQLException e) {
           throw new RuntimeException(e);
       } catch (ClassNotFoundException e) {
           throw new RuntimeException(e);
       }

   }
}

class DCNDemoListener implements DatabaseChangeListener
{
   public void onDatabaseChangeNotification(DatabaseChangeEvent e)
   {
       System.out.println("Caught");
       System.out.println(e.toString());
   }
}

Output of this is : TMCDB7.TEMP is part of the registration.

Issue Facing :

The problem I am facing here is I am able to register in the database for table to get notification for update in the table but I am getting any notification in the Java application. I have checked USER_CHANGE_NOTIFICATION_REGS where am able to see the registration existence. In database side logs I can see encountered ORA-12535 for some time. But later on stopped coming this error . Also checked for any failure on database, But no other error found . I am connecting to database over VPN.

Does anyone have any idea regarding this issue to move forward.

Workaround tried to resolve issue : 1.Tried increase the timeout for ORA-12535 2.check for 1521 port whitelisting on the server to allow connection 3.Tried connecting to database using IP address in-stead of hostname 4.Tried adding below properties and checked but no luck

prop.setProperty(OracleConnection.DCN_BEST_EFFORT,"true");

prop.setProperty(OracleConnection.NTF_LOCAL_TCP_PORT,"3624");

prop.setProperty(OracleConnection.NTF_LOCAL_HOST,"<IP Address>");

5. Upgraded from Java 1.8 to Java 11 and ojdbc 6 to 11

6. updated queuesize for listerner as QUEUESIZE=99