No notifications using Continuous Query Notification (CQN) after some idle time

user3069926

Hello,

I am using ODP.NET core to register Continous Query Notification with Oracle Database 12c R2. This works and event handler is called on change , however after some idle time (typically during off business hours when there are no updates), Events/notifications are no longer received and netstat doesnt show port being opened between Database and Application Server. This requires restarting the process (i.e. Windows service) every day. Is their any workaround or resolution for this ?

protected override async Task ExecuteAsync(CancellationToken stoppingToken)

        { 

         

                if(ExecutionFlag)

                {

                        ExecutionFlag = false;

                        _logger.LogInformation("Worker Started at: {0}", DateTimeOffset.Now);

                          

                        OracleConfiguration.DbNotificationPort = 1201;

                        OracleDependency dep = null;

                        OracleConnection con = null;

                        try

                        {

                            con = new OracleConnection(oraConnection);

                            OracleCommand objCommand = new OracleCommand("select * from fins_authorizationmaster", con);                 

                            con.Open();

                            dep = new OracleDependency(objCommand);                           

                            objCommand.AddRowid = true;

                            objCommand.Notification.IsNotifiedOnce = false;

                            dep.OnChange += new OnChangeEventHandler(Worker.dep_OnChange);

                            objCommand.ExecuteNonQuery();

                        }

                        catch (System.Exception ex)

                        {              

                            _logger.LogError("Error on Worker Page -  ExecuteAsync {0}", ex.Message + "|" + ex.InnerException + "|" + ex.StackTrace);                           

                        }                     

                }                 

        }

Alex Keh-Oracle

Perhaps the CQN registration is timing out. The default value lasts about 14 hours.

https://docs.oracle.com/en/database/oracle/oracle-data-access-components/19.3/odpnt/NotificationRequestTimeout.html#GUID…

You should still get a notification if a time out occurs.