Oracle database change notification in Quarkus 3.0.3.Final

[silicon-selenion]

I'm new to oracle database change notification. using this code i get exception when execution is on stmt.execute("SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME FROM EMPLOYEES e WHERE e.EMPLOYEE_ID > 1"); I need help on this.

@ApplicationScoped
public class ChangeRegistrar {

    @Inject
    Logger log;

    @Inject
    DatabaseChangeNotificationListener listener;

    @Inject
    DataSource dataSource;

    private DatabaseChangeRegistration dcr;
    private OracleConnection conn;

    public synchronized void register() throws SQLException {

        try {
            this.conn = this.dataSource.getConnection().unwrap(OracleConnection.class);

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

            this.dcr.addListener(listener);
            OracleStatement stmt = this.conn.createStatement().unwrap(OracleStatement.class);
            stmt.setDatabaseChangeRegistration(this.dcr);
            // ResultSet rs = stmt.executeQuery("select * from employees");
            if (dcr != null && dcr.getState().equals(NotificationRegistration.RegistrationState.ACTIVE)) {
                stmt.execute("SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME FROM EMPLOYEES e WHERE e.EMPLOYEE_ID > 1");
                ResultSet rs = stmt.getResultSet();
                while (rs.next()) {
                }
                String[] tableNames = this.dcr.getTables();
                for (int i = 0; i < tableNames.length; i++) {
                    System.out.println(tableNames[i] + " is part of the registration.");
                }
                rs.close();
            } else {
                log.fatal("Houston we got a problem here");
            }

            stmt.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            unregister();
            throw ex;
        }
    }

// rest of the code here
}

Exception

ORA-29970: Specified registration id does not exist

java.sql.SQLException: ORA-29970: Specified registration id does not exist

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:123)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:969)
        at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1271)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1149)
        at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1661)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1470)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2505)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2460)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:328)
        at io.winigalab.oraclechangealert.ChangeRegistrar.register(ChangeRegistrar.java:56)
        at io.winigalab.oraclechangealert.ChangeRegistrar_Subclass.register$$superforward(Unknown Source)
        at io.winigalab.oraclechangealert.ChangeRegistrar_Subclass$$function$$14.apply(Unknown Source)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:74)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:63)
        at io.quarkus.arc.runtime.devconsole.InvocationInterceptor.proceed(InvocationInterceptor.java:62)
        at io.quarkus.arc.runtime.devconsole.InvocationInterceptor.monitor(InvocationInterceptor.java:49)
        at io.quarkus.arc.runtime.devconsole.InvocationInterceptor_Bean.intercept(Unknown Source)
        at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:38)
        at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:26)
        at io.winigalab.oraclechangealert.ChangeRegistrar_Subclass.register(Unknown Source)
        at io.winigalab.oraclechangealert.ChangeRegistrar_ClientProxy.register(Unknown Source)
        at io.winigalab.oraclechangealert.ChangeAlert.onStart(ChangeAlert.java:29)
        at io.winigalab.oraclechangealert.ChangeAlert_Subclass.onStart$$superforward(Unknown Source)
        at io.winigalab.oraclechangealert.ChangeAlert_Subclass$$function$$13.apply(Unknown Source)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:74)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:63)
        at io.quarkus.arc.runtime.devconsole.InvocationInterceptor.proceed(InvocationInterceptor.java:62)
        at io.quarkus.arc.runtime.devconsole.InvocationInterceptor.monitor(InvocationInterceptor.java:49)
        at io.quarkus.arc.runtime.devconsole.InvocationInterceptor_Bean.intercept(Unknown Source)
        at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:38)
        at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:26)
        at io.winigalab.oraclechangealert.ChangeAlert_Subclass.onStart(Unknown Source)
        at io.winigalab.oraclechangealert.ChangeAlert_Observer_onStart_a5b14d2eeffca495fe788bb5728af3d8c5de83f3.notify(Unknown Source)
        at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:346)
        at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:328)
        at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:82)
        at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:155)
        at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:106)
        at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy_0(Unknown Source)
        at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy(Unknown Source)
        at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
        at io.quarkus.runtime.Application.start(Application.java:101)
        at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:111)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:44)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:124)
        at io.quarkus.runner.GeneratedMain.main(Unknown Source)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
        at java.base/java.lang.reflect.Method.invoke(Method.java:578)
        at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:104)
        at java.base/java.lang.Thread.run(Thread.java:1623)
Caused by: Error : 29970, Position : 0, Sql = SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME FROM EMPLOYEES e WHERE e.EMPLOYEE_ID > 1, OriginalSql = SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME FROM EMPLOYEES e WHERE e.EMPLOYEE_ID > 1, Error Msg = ORA-29970: Specified registration id does not exist


        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
        ... 58 more
2023-05-23 20:49:37,943 INFO  [io.quarkus] (Quarkus Main Thread) oracle-change-alert 1.0.0-SNAPSHOT on JVM (powered by Quarkus 3.0.3.Final) started in 20.468s. Listening on: http://localhost:8080
2023-05-23 20:49:37,945 INFO  [io.quarkus] (Quarkus Main Thread) Profile dev activated. Live Coding activated.
2023-05-23 20:49:37,946 INFO  [io.quarkus] (Quarkus Main Thread) Installed features: [agroal, cdi, narayana-jta, smallrye-context-propagation, vertx]
2023-05-23 20:49:37,949 INFO  [io.qua.dep.dev.RuntimeUpdatesProcessor] (Aesh InputStream Reader) Live reload total time: 20.672s```

[maxandersen]

What exception are you getting ? Also Not spotting anything quarkus related as such in the code. Would be good with some more context?