Change Notification with Oracle Dependency in C #

Question

I am trying to get an oracle database replacement notification in C #. I found this tutorial on how to get these notifications. I also created a simple win form notification notification application.

public partial class Form1 : Form { public Form1() { InitializeComponent(); SetUpNotification(); } private void SetUpNotification() { var login = "DATA SOURCE=XE;PERSIST SECURITY INFO=True;USER ID=USR;PASSWORD=PWD"; OracleConnection conn = null; OracleDependency dep = null; try { conn = new OracleConnection(login); var cmd = new OracleCommand("select * from customer_details", conn); conn.Open(); cmd.AddRowid = true; dep = new OracleDependency(cmd); cmd.Notification.IsNotifiedOnce = false; dep.OnChange += new OnChangeEventHandler(dep_OnChange); } catch(Exception e) { MessageBox.Show(e.Message, e.Source, MessageBoxButtons.OK, MessageBoxIcon.Error); } } void dep_OnChange(object sender, OracleNotificationEventArgs eventArgs) { MessageBox.Show(eventArgs.Details.ToString(), "Database changed"); } } 

The input line and command line of OracleCommand are 100% good. I also tried programmatically changing the database, changing the data in the database, but did not fire the OnChange event.

I also provided notifications to the USR user

 grant change notification to USR 

and job_queue_processes is greater than zero.

Maybe the problem is that I am using oracle Database Express Edition ?

Answer 1

I have found a solution. The problem is not the release of the Oracle database, but the version. The Oracle server version was 11.2.0.2.0 , and the client version was 11.2.0.3.0 , and this slight difference caused the problem.

So, for the future, always check the client and server versions when a similar problem occurs.

Answer 2

Also you missed cmd.ExecuteNonQuery() in your code. Without the command, this code will not work regardless of the version of the database.

 var cmd = new OracleCommand("select * from new_place", conn); conn.Open(); cmd.AddRowid = true; var dep = new OracleDependency(cmd); dep.OnChange += new OnChangeEventHandler(dep_OnChange); cmd.ExecuteNonQuery();