CODEWITHSUNDEEP
oracle-databaseodp.net
BKN
BKN

Reputation: 56

ODP.NET Query based notification in Oracle

am trying to use the query based notifications, but it seems to switch over to the object based notification for some reason. My environment is Oracle 11.2 ( I believe you need 11.1 or above for query based to work) and I have a select statement which retrieves a column which is of type NUMBER. (Again here my understanding is that only varchar2 and number columns work). I seem to get every change notification on the table and not just my flltered dataset

As an example I've created a sample table called test_db_notification with a single column of type NUMBER.

Added two entries in there as follows:

SELECT rowid,column1 FROM test_db_notification;

ROWID                 COLUMN1

AAAERnAAKAAEurNAAI       54 
AAAERnAAKAAEurPAAA       63

Here's my code to register for notification:

OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = " SELECT column1 FROM test_db_notification where column1 = 63";
_dep = new OracleDependency(_cmdObj);
_dep.QueryBasedNotification = true; 
_dep.OnChange += new OnChangeEventHandler(_dep_OnChange);
_cmdObj.Notification.IsNotifiedOnce = false;
_cmdObj.AddRowid = true;
_cmdObj.ExecuteNonQuery();

When I register and change the value of column1 where the current value is 54, I get back a notification saying the ROWID for 54 has been updated. I was expecting not to receive any notifications since my original query looks only at column1 = 63.

Am I missing something here?

Thanks

Upvotes: 3

Views: 1080

Answers (2)

codefolk
codefolk

Reputation: 36

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

Upvotes: 0

Jana Sattainathan
Jana Sattainathan

Reputation: 825

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.

Upvotes: 1

Related Questions