CODEWITHSUNDEEP
c#oracle-database
ultraviolet
ultraviolet

Reputation: 95

oracle change notification

I am trying to get a test application to work using Oracle Change Notification with C#, but I am not receiving the callback notification in my application. Oracle DB version is 11.2.0.1.0. Oracle.DataAccess v.2.112.30. I can confirm the query gets registered in Oracle by viewing SYS.USER_CHANGE_NOTIFICATION_REGS and SYS.USER_CQ_NOTIFICATION_QUERIES. However, nothing ever appears in SYS.DBA_CHANGE_NOTIFICATION_REGS.

The registration persists until I commit a transaction on the table. The registration disappears after a few seconds after the commit and my application does not received the notification.

I have made sure my computer is listening on the correct port and have even tried turning off any firewall that could be blocking the port.

I do have GRANT CHANGE NOTIFICATION to MYSCHEMA, GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO MYSCHEMA, and the JOB_QUEUE_PROCESSES is set to 1.

Questions:

1) Should the registration be visible in SYS.DBA_CHANGE_NOTIFICATION_REGS and, if so, what could be causing it not to be when it is visible in SYS.USER_CHANGE_NOTIFICATION_REGS and SYS.USER_CQ_NOTIFICATION_QUERIES?

2) What could be causing the registration to disappear after a commit?

3) What could be causing the failure of the notification to my application?

Here is the C# code I am using and it is basically the same as from the Oracle website:

using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;

namespace NotifyTest
{
    public class Program
    {
        public static bool IsNotified = false;

            public static void Main(string[] args)
        {
            string constr = "User Id=mySchema;Password=myPassword;Data Source=myOracleInstance";

            OracleDependency dep = null;

            try
            {
                using (var con = new OracleConnection(constr))
                {
                    Console.WriteLine("Registering query...");
                    var cmd = new OracleCommand("select * from mySchema.NOTIFY_TEST", con);
                    con.Open();

                    OracleDependency.Port = 1005;
                    dep = new OracleDependency(cmd);
                    dep.OnChange += OnMyNotificaton;

                    int queryRegistered = cmd.ExecuteNonQuery();

                    // If oracle returns -1, then the query is successfully registered
                    if (queryRegistered == -1)
                    {
                        Console.WriteLine("Query Registered...");
                        Console.WriteLine("Listening for Callback...");
                    }
                    else
                    {
                        Console.WriteLine("There was an error...");
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // Loop while waiting for notification       
            while (IsNotified == false)
            {
                Thread.Sleep(100);
            }
        }

        public static void OnMyNotificaton(object sender, OracleNotificationEventArgs arg)
        {
            Console.WriteLine("Table change notification event is raised");
            Console.WriteLine(arg.Source.ToString());
            Console.WriteLine(arg.Info.ToString());
            Console.WriteLine(arg.Source.ToString());
            Console.WriteLine(arg.Type.ToString());
            IsNotified = true;
        }
    }
}

Upvotes: 2

Views: 5275

Answers (3)

user241841
user241841

Reputation: 23

In SYS.CHNF$_REG_INFO attribute QOSFLAGS there is QOS_DEREG_NFY, which specifies that the database should unregister the registration on the first notification.

Upvotes: 1

user123664
user123664

Reputation:

Not sure but the value on job_queue_processes (1) is a bit low. Oracle performs all kinds of maintenance and event handling tasks internally. For this they also use Job slaves. Raise job_queue_processes (default 1000) and check Troubleshooting CQN Registrations

Upvotes: 0

ultraviolet
ultraviolet

Reputation: 95

Just wanted to provide an update as to how I resolved this issue. I changed my Oracle.DataAccess.dll from v.2.112.3.0 to v.2.112.1.2 and it works fine.

Upvotes: 2

Related Questions