Oracle 11g Continuous Query Notification to a vs 2005 c# service

[livez]

Hello! Im trying to set up a Continuous Query Notification between our oracle database and my c# service, but can´t seem to get it to work. This is the method I have for making the connection:

private void SubscribeToTable(string tableName)
{
Log.Write("Add listener: " + tableName);

        OracleConnection con = DataBase.GetOpenConnection();
        OracleDependency dependency = new OracleDependency();
        dependency.QueryBasedNotification = false;
        OracleCommand cmd = con.CreateCommand();
        cmd.CommandText = "Select \* from " + tableName;
        dependency.OnChange += new OnChangeEventHandler(dependency\_OnChange);
        dependency.AddCommandDependency(cmd);
        cmd.Notification.IsNotifiedOnce = false;
        cmd.AddRowid = true;
        cmd.ExecuteNonQuery();
        con.Close();
        con.Dispose(); 
    }

And when I run this I get a row in user_change_notification_regs with these values:

42 4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.119)(PORT=1521))?PR=0 0 0 49999 WEB.EXPORTEDDOCUMENTS

and this is the callback event:

static void dependency_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
Log.Write("Table has been Changed");
Log.Write(eventArgs.Source.ToString());
Log.Write(eventArgs.Info.ToString());
Log.Write(eventArgs.Source.ToString());
Log.Write(eventArgs.Type.ToString());
}

My problem is the callback-event never runs. Has anyone else had this problem?

[sonosun]

Hello, Have you solved this issue? I am having a similar problem. I used a OCI sample code from ORACLE site. My OCI client was able to register the subscription. However, the subscription was removed from USER_CHANGE_NOTIFICATION_REGS as soon as I updated the table that is registered for subscription. And, my client callback function never got called. Btw, I used oracle client 11.2.0.1.0 and my db is running 11.2.0.1.0. The sample code was compiled on Linux Centos 5.5. I checked my user privilege and it has CHANGE_NOTIFICATION. I also check "job_queue_processes" and "dml_locks", both of them have non-zero values.

[livez]

Hello! Sadly I never solved it. My subscription was also removed eventhough I set it up to stay (cmd.Notification.IsNotifiedOnce = false;), and I can see with network sniffer that something is sent to my computer from databasecomputer, but my application never registers the callback. I also tried with samplecode from oracle (http://www.oracle.com/technetwork/issue-archive/win06odp-099708.html[^] and http://www.devart.com/dotconnect/oracle/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleDependency.html[^]) but I experienced same problem with that. I used oracle client 11.2.0.2.0 and my db is running 11.2.0.1.0. I´ve shelved this issue for a while but figured I'd give it another go in a few weeks. If you or anyone else solve this issue I would very much appreciate a reply here, and I will of course do the same. /regards

modified on Thursday, February 10, 2011 3:53 AM

[sonosun]

Thanks for the reply. If I solve this issue, I will post it here.

[sonosun]

Hi, May I know what Oracle edition that you have? Is it Enterprise or Standard edition? Mine is standard edition. I am trying to find out if publish/subscribe is supported in standard edition.

[sonosun]

Hello, I managed to get my subscription working. Here is what I have done on my test environment. My client uses oracle-instant-client 11.2.0.1. My db server is running Oracle 11.2.0.1 release. Both client and server are on Centos OS. Previously, I have client on Centos and server on Solaris. I have done quite few tests with combination of different client, server and their OS. I found that the Oracle version and the OS platform contributed to the issue that I had. As a preliminary quick test, I also ran my client on the same box where my db server was running. In my case, this worked fine. Hopefully, this will help. Cheers, sw