Continuous Query Notification is automatically unregistered

Question

I am working with Oracle and ODP.Net and registering some continuous query notifications.

Everything worked fine untile the database was upgraded to 11.2.0.3 - now I see that my queries are registered and deregistered after a few seconds. The code looks like this:

        OracleCommand cmd = new OracleCommand();
        cmd.Connection = getOpenConnection();
        cmd.CommandText = _sqlStatement;

        _odep = new OracleDependency(cmd, false, 0, false);

        _odep.OnChange += new OnChangeEventHandler(_odep_OnChange);

        OracleDB.bindVars(ref cmd, arguments);

        cmd.Notification.IsNotifiedOnce = false;
        cmd.ExecuteNonQuery();

Is there a log for CQN registration somewhere on the database?

Edit: getOpenConnection() is just a function that returns an open connection, while OracleDB.bindVars just binds the existing bind variables to the command. Emphasis on the fact that everything worked fine before the DB update :)

Could you check what you get as a result from SELECT * FROM DBA_CHANGE_NOTIFICATION_REGS (after you registered your notification) — Eggi
– Eggi, Commented Mar 9, 2012 at 6:11
Hi Eggi, thanks for you answer. When I execute that sql command, I see that the queries are successfully registered - but they disappear if I repeat the query a minute after. Could it be related to the ODP.Net driver version? — Andrea
– Andrea, Commented Mar 9, 2012 at 13:56
Maybe you could try to use 11.2.0.3 (http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html). Do you even get any notifications? Maybe there was some change regarding a firewall (on the machine that runs the client - because the server connects to the client) or something. — Eggi
– Eggi, Commented Mar 9, 2012 at 14:12
I think I can rule out everything that is not related to versioning - the only thing that has changed is the version of Oracle. I will try the 11.2.0.3 as soon as I get my hands back on the machine. — Andrea
– Andrea, Commented Mar 9, 2012 at 16:12

Andrea · Accepted Answer · 2012-03-15 08:39:17Z

2

In the end, it was a problem on the database side - incompatibility caused by registering CQN entries using the 11.2.0.1 Client on a 11.2.0.3 Server.

answered Mar 15, 2012 at 8:39

Andrea

8941 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christian Shay · Accepted Answer · 2014-02-07 21:23:25Z

2

This may not pertain to your specific scenario but anyone having problems with CQN (and AQ) notifications not occuring should upgrade the database. There was a bug in 11.2.0.1 database.

The following SQL causes Oracle to avoid the buggy code path with the older version, but you are advised to upgrade the database:

alter system set events '10867 trace name context forever, level 1';

EDIT: Please note that this is an old bug and the problem is fixed in current patchsets. You will need to patch BOTH the database and ODP.NET to fix it.

Christian Shay

Oracle

edited Feb 7, 2014 at 21:23

answered Mar 16, 2012 at 8:30

Christian Shay

2,63315 silver badges25 bronze badges

1 Comment

Andrea Over a year ago

It was not actually my case, but thanks for the information - surely it will be useful to someone!

Joseph Simpson · Accepted Answer · 2012-06-26 14:54:22Z

1

+1 for Christian's answer (I don't have the rep to +1 it)

His fix (alter system set events '10867 trace name context forever, level 1') resolved the issue of callback events not firing.

Until that fix was applied I would see the change notification registration enter the USER_CHANGE_NOTIFICATION_REGS table and disappear when a DB change occurred, but no event would be fired.

I am using a 11.2.0.1.0 server and 11.2.0.3.0 client (http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html).

answered Jun 26, 2012 at 14:54

Joseph Simpson

4,2131 gold badge27 silver badges29 bronze badges

Collectives™ on Stack Overflow

Continuous Query Notification is automatically unregistered

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related