ODP.NET Query based notification in Oracle

It is never too late to post answers on StackOverflow since people (myself included) keep searching for things years from when the question was asked. This may not be the answer you are looking for but consider it an observation that might lead you in the right direction. I do not have enough points to post "comments", so I am typing this as an answer.

To recreate the problem, I created a table

CREATE TABLE TEST_DB_NOTIFICATION
(
  COLUMN1  VARCHAR2(1 BYTE),
  COLUMN2  VARCHAR2(1 BYTE)
)

I used the following .Net code (minor changes to yours)

private void button1_Click(object sender, RoutedEventArgs e)
{
    OracleConnection _connObj = new OracleConnection("Data Source=mydb;User Id=myid;Password=mypassword;");
    _connObj.Open();
    OracleCommand _cmdObj = _connObj.CreateCommand();
    _cmdObj.CommandText = " SELECT column1 FROM ods.test_db_notification where column1 = 'a' ";
    OracleDependency _dep = new OracleDependency(_cmdObj);
    _dep.QueryBasedNotification = true;
    _dep.OnChange += new OnChangeEventHandler(_dep_OnChange);
    _cmdObj.Notification.IsNotifiedOnce = false;
    _cmdObj.AddRowid = true;
    _cmdObj.ExecuteNonQuery();
}

void _dep_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
    MessageBox.Show("changed" + eventArgs.Details.Rows.Count);
}

Basically, I am doing the same thing as you are but I am watching for Column1='a' instead.

What I noticed is below:

** INSERT behavior **

1) When I insert into the table, I get notified when the new row has Column1 value = 'a'. 2) I do not get notified when the Column1 value is not 'a'.

** UPDATE behavior **

1) When I update the table resulting in existing values in Column1 changing to 'a' from a non-'a' value, I get notified. 2) I get notified why I change columns whose value is already 'a' to be 'a' again. 3) I get notified if I change values from 'a' to 'z' on Column1.

** DELETE behavior **

1) I get notified when I delete rows with Column1 value = 'a' 2) I do not get notified when Column1 value is not 'a'

This leads me to the obvious conclusion that if there are any changes to rows qualifying as the results of the statement you are watching (including changes that are overwrites of existing values to itself), you get a notification. This is consistent with the behavior of INSERT, UPDATE and DELETE.

Inserts are not captured by query based notification afaik. If you'd like to capture those set your _dep.QueryBasedNotification to false (which is the default).