oracle 12c的更改通知问题 [英] Change notification Issue with oracle 12c
问题描述
OracleCommand cmd =
new OracleCommand("select * from Test WHERE TestFLAG = 1 or TestFLAGis not null", con);
当表中有更改时，无论情况如何，我的.net项目仍将收到通知.
When there is a change at the table, no matter the condition is, my .net project will still receive notification.
对于第二个问题，在我第一次收到任何通知后，此后在表上的任何更改都不会得到通知.为什么?
For second issue, After I receive any notification for 1st time, any changes on the table after that are not being notified. Why?
我的问题有解决办法吗?
Any solution for my problem?
public class MyNotificationSample
{
static string constr = "your db INFO";
public static bool IsNotified = false;
static OracleDependency dep = null;
public static void Main(string[] args)
{
//To Run this sample, make sure that the change notification privilege
//is granted to scott.
OracleConnection con = null;
try
{
con = new OracleConnection(constr);
OracleCommand cmd = new OracleCommand("select * from Test WHERE TestFLAG = 1 or TestFLAGis not null", con);
con.Open();
// Set the port number for the listener to listen for the notification
// request
OracleDependency.Port = 1005;
// Create an OracleDependency instance and bind it to an OracleCommand
// instance.
// When an OracleDependency instance is bound to an OracleCommand
// instance, an OracleNotificationRequest is created and is set in the
// OracleCommand's Notification property. This indicates subsequent
// execution of command will register the notification.
// By default, the notification request is using the Database Change
// Notification.
dep = new OracleDependency(cmd);
// Add the event handler to handle the notification. The
// OnMyNotification method will be invoked when a notification message
// is received from the database
dep.OnChange += OnMyNotificaton;
// The notification registration is created and the query result sets
// associated with the command can be invalidated when there is a
// change. When the first notification registration occurs, the
// notification listener is started and the listener port number
// will be 1005.
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
con.Close();
Console.Write("Press Any Key to Quit");
Console.ReadLine();
// Loop while waiting for notification
}
public static void OnMyNotificaton(object src,
OracleNotificationEventArgs arg)
{
if (dep.HasChanges)
{
Console.WriteLine("Notification Received");
DataTable changeDetails = arg.Details;
Console.WriteLine("Data has changed in {0}",
changeDetails.Rows[0]["ResourceName"]);
}
}
最新更新:使监听器永不过期.
Latest Update: TO make the listener never expired.
new OracleDependency(cmd, false, 0 , true);
但是，我的查询仍然无法正常工作...
But, my query still doesn't work...
推荐答案
您的查询具有以下WHERE子句:TestFLAG = 1 or TestFLAGis not null.
TestFLAG和is not null之间可能缺少空格.在这种情况下，表达式的第一部分是不必要的，如TestFLAG = 1时，则它不是null.
也许问题在于，您的查询覆盖的范围超出了您的预期.
Your query has this WHERE clause: TestFLAG = 1 or TestFLAGis not null.
There's probably a missing space between TestFLAG and is not null. In that case, the first part of the expression is unnecessary, as when TestFLAG = 1, then it's not null.
Maybe the problem is, that your query covers much more ground, than you intended.
除此之外，Oracle的数据库更改通知功能不能保证，您只会收到有关查询实际返回的行的通知.它保证，您将获得有关这些行的通知，但您也可能会获得误报"，因此实际上与不匹配的行的通知将得到通知.
Apart from that, the Oracle's Database Change Notifications feature does not guarantee, that you will only get notifications for the rows actually returned by the query. It guarantees, that you will get notifications for those rows, but you can also get "false positives", so notifications for rows which actually do not match your query.
这可能是 Oracle的很好解释.文件(重点是我):
This may be a good explanation from the Oracle Docs (emphasis mine):
基于查询的注册有两种模式:保证模式和 尽力而为模式.在保证模式下，任何数据库更改通知 确保查询中包含的内容发生更改 结果集.但是，如果查询很复杂，则不能 在保证模式下注册.在这种情况下，将使用尽力而为"模式.
Query-based registrations have two modes: guaranteed mode and best-effort mode. In guaranteed mode, any database change notification ensures that a change occurred to something contained in the queried result set. However, if a query is complex, then it cannot be registered in guaranteed mode. Best-effort mode is used in such cases.
尽力而为模式简化了基于查询的注册的查询.不 简化会丢失通知.但是，那 简化可能导致误报，因为较简单的版本 当原始查询结果可能会更改查询结果 仍然没有限制. 尽力而为模式基于查询的注册.在这种情况下，开发人员 可以使用基于对象的注册，它可以注册大多数查询 类型.基于对象的注册会在以下情况下生成通知: 即使实际查询结果没有更改，查询对象也会更改.这 也意味着基于对象的注册更容易出错 比基于查询的注册更具优势.开发人员应注意 每个数据库的相对优势和劣势的变化 通知选项，然后选择最适合他们的 要求.
Best-effort mode simplifies the query for query-based registration. No notifications are lost from the simplification. However, the simplification may cause false positives, as the simpler version's query result could change when the original query result would not.There still remain some restrictions on which queries can have best-effort mode query-based registrations. In such cases, developers can use object-based registrations, which can register most query types. Object-based registrations generate notifications when the query object changes, even if the actual query result does not. This also means that object-based registrations are more prone to false positives than query-based registrations. Developers should be aware of the relative strengths and weaknesses of each database change notification option and choose the one that best suits their requirements.
在第二个问题上，如 @ user1415516 所写，您需要将通知设置为在第一次通知后不注销，因此请在执行命令之前添加cmd.Notification.IsNotifiedOnce = false;.
On the second issue, as @user1415516 wrote, you need to set your notification to not get unregistered after first notification, so add cmd.Notification.IsNotifiedOnce = false; before you execute the command.
这篇关于oracle 12c的更改通知问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
