Change notification Issue with oracle 12c

Question

OracleCommand cmd =
new OracleCommand("select * from Test WHERE TestFLAG = 1 or TestFLAGis not null", con);

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...

Answer 1

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.

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.

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.

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.

Answer 2

add this to your code

cmd.Notification.IsNotifiedOnce = false;