I am trying to get a notification in ASP.Net console application whenever a column value changes in a specific database column using OracleDependency. Notification gets Registered and inserted in USER_CHANGE_NOTIFICATION_REGS table but onChange event is not triggered when row is updated in DB.
Additionally, I have granted following privilege to user:
grant change notification to hasan;
and my firewall is also currently disabled.
Below is the code to register query:
string constr = "User ID=hasan;Data Source=10.32.157.5:1521/iwin;Password=hasan; Min Pool Size=1; Connection Lifetime = 120;";
OracleConnection con = null;
OracleDependency dep = null;
con = new OracleConnection(constr);
OracleCommand cmd = new OracleCommand("select * from hasan.Emp", con);
con.Open();
OracleDependency.Port = 2010;
dep = new OracleDependency(cmd);
dep.OnChange +=
new OnChangeEventHandler(OnMyNotificaton);
OracleDataAdapter ad = new OracleDataAdapter(cmd);
cmd.ExecuteNonQuery();
OracleTransaction txn = con.BeginTransaction();
string updateCmdText =
"update hasan.Emp empl set empl.empId = empl.empId + 1";
OracleCommand updateCmd = new OracleCommand(updateCmdText, con);
updateCmd.ExecuteNonQuery();
txn.Commit();
Event Listener:
public static void OnMyNotificaton(object src,
OracleNotificationEventArgs arg)
{
Console.WriteLine("Notification Received");
DataTable changeDetails = arg.Details;
Console.WriteLine("Data has changed in {0}",
changeDetails.Rows[0]["ResourceName"]);
IsNotified = true;
}