Java Database Connectivity (JDBC)

Hi All,

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 :

1. grant change notification to <user>;

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

1. 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;
2. 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 :

1. Tried increase the timeout for resolving 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 database listener as QUEUESIZE=99