- 3,716,128 Users
- 2,242,960 Discussions
- 7,845,836 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 479 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 5 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 417 SQLcl
- 42 SQL Developer Data Modeler
- 184.9K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 9 DevOps
- 3K QA/Testing
- 259 Java
- 6 Java Learning Subscription
- 11 Database Connectivity
- 67 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 31 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 11 Español
- 1.9K Japanese
- 2 Portuguese
Change Notification Clarification: OracleNotificationEventArgs Doesn't Include Delete When Updates o
Hi,
I've recently started testing the Change Notification functionality for an application that I am working on. I'm getting the notifications, but not in the manner I expected, however this may be normal behavior.
I've noticed that when I delete rows I get the notification. However If I insert or update and also delete, I do not get the delete notification.
If I insert and update in a single operation I get notifications of both. It seems that the DELETE notification is ignored if it is in combination with either of the other two. I was wondering if this is expected behavior or if I have missed something in my implementation.
Regards,
Oracle.ManagedDataAccess Version: 4.122.18.3
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Best Answer
-
Well I upgraded to XE 18c and the change notifications worked as expected.
Regards,
Answers
-
Is the DB itself sending a DELETE notification? If so, does the ODP.NET trace show that it receives a DELETE notification?
-
Alex,
Honestly I've not heard of ODP.Net TRACE before your question, but managed to read a bit and get it enabled in the app.config file.
The output doesn't indicate what the notification is, from what I can tell.
Here's a section...
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadFromBufferedData()
2020-02-21 15:27:14.932044 TID:6 (PRI) (BUF) (COBP.GET) (poolid:3) (key:4118) (bufid:18) (count:6) (NotificationHandler.ReadFromNetwork)
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.UnmarshalNSDataPacket()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadByte()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadBuffer()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadBuffer()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadShort()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadInt()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadBuffer()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.ReadBuffer()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (EXT) NotificationHandler.UnmarshalNSDataPacket()
2020-02-21 15:27:14.932044 TID:12 (PRI) (ENT) OracleDependency.SetNotificationDetails()
2020-02-21 15:27:14.932044 TID:11 (PRI) (SVC) (ENT) NotificationHandler.ReadFromBufferedData()
2020-02-21 15:27:14.932044 TID:12 (PRI) (ENT) OracleDependency.GetOracleDependencyFromNTFNId()
2020-02-21 15:27:14.932044 TID:12 (PRI) (EXT) OracleDependency.GetOracleDependencyFromNTFNId()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ctor()
2020-02-21 15:27:14.932044 TID:11 (PRI) (BUF) (COBP.PUT) (poolid:3) (key:4118) (bufid:19) (count:7) (OraBuf.ReturnToPool)
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ctor()
2020-02-21 15:27:14.932044 TID:12 (PRI) (ENT) OracleNotificationEventArgs.ctor()
2020-02-21 15:27:14.932044 TID:12 (PRI) (EXT) OracleNotificationEventArgs.ctor()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ParseNotificationInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.SetNotificationTypeSourceInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (EXT) NotificationDetails.SetNotificationTypeSourceInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ReadQueryInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ReadTableInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ReadRowInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.AddRowDetail()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (EXT) NotificationDetails.AddRowDetail()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ReadRowInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ReadRowInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.AddRowDetail()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (EXT) NotificationDetails.AddRowDetail()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ReadRowInfo()
2020-02-21 15:27:14.932044 TID:12 (PRI) (SVC) (ENT) NotificationDetails.ReadRowInfo()
2020-02-21 15:27:14.933042 TID:12 (PRI) (SVC) (ENT) NotificationDetails.AddRowDetail()
2020-02-21 15:27:14.933042 TID:12 (PRI) (SVC) (EXT) NotificationDetails.AddRowDetail()
2020-02-21 15:27:14.933042 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ReadRowInfo()
2020-02-21 15:27:14.933042 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ReadTableInfo()
2020-02-21 15:27:14.933042 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ReadQueryInfo()
2020-02-21 15:27:14.933042 TID:12 (PRI) (SVC) (EXT) NotificationDetails.ParseNotificationInfo()
2020-02-21 15:27:14.933042 TID:12 (PUB) (ENT) OracleDependency.FiredEvent()
2020-02-21 15:28:02.172911 TID:4 (PRI) (ENT) (CP) PoolManager.RemoveRM()
I set TraceLevel = 7 and get this additional info...this was a delete.
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) New receive packet. Header:
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 AC 00 00 06 00 00 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 |.. |
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 02 00 00 00 1E 19 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 02 03 69 19 00 00 |....i...|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 02 00 01 19 00 00 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 02 00 02 17 00 00 00 04 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 00 01 00 00 00 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 78 17 00 00 00 07 43 |.x.....C|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 48 4E 46 39 30 36 17 00 |HNF906..|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 01 02 17 00 00 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 61 00 01 1A 13 00 00 00 |a.......|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 07 00 02 58 45 0A |.....XE.|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 11 00 B3 23 00 00 00 |....#...|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 00 00 00 00 01 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 56 00 00 00 00 00 |..V.....|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 00 07 00 01 00 00 00 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 08 00 11 43 54 50 52 50 |...CTPRP|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 2E 44 45 50 41 52 54 4D |.DEPARTM|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 45 4E 54 53 00 00 B5 51 |ENTS...Q|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 00 01 00 00 00 08 00 12 |........|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 41 41 41 4C 56 52 41 41 |AAALVRAA|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 45 41 41 41 41 31 2F 41 |EAAAA1/A|
2020-02-21 17:37:26.527147 TID:11 (NET) (REC) 41 43 |AC |
and this was an insert...
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) New receive packet. Header:
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 AC 00 00 06 00 00 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 |.. |
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 02 00 00 00 1E 19 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 02 03 69 19 00 00 |....i...|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 02 00 01 19 00 00 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 02 00 02 17 00 00 00 04 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 00 01 00 00 00 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 78 17 00 00 00 07 43 |.x.....C|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 48 4E 46 39 30 36 17 00 |HNF906..|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 01 02 17 00 00 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 61 00 01 00 00 00 00 00 |a.......|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 07 00 02 58 45 01 |.....XE.|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 06 00 AD 12 00 00 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 00 00 00 00 01 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 56 00 00 00 00 00 |..V.....|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 00 07 00 01 00 00 00 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 02 00 11 43 54 50 52 50 |...CTPRP|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 2E 44 45 50 41 52 54 4D |.DEPARTM|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 45 4E 54 53 00 00 B5 51 |ENTS...Q|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 00 01 00 00 00 02 00 12 |........|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 41 41 41 4C 56 52 41 41 |AAALVRAA|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 45 41 41 41 41 31 39 41 |EAAAA19A|
2020-02-21 17:39:01.893822 TID:11 (NET) (REC) 41 46 |AF |
So as long as I don't do a delete and insert in the same transaction, then I receive the DELETE notification. If I include both, I only get the insert notification.
Regards
-
I guess ODP.NET doesn't output that info to the trace. Darn.
The two possibilities I see is that either Continuous Query Notification (aka Change Notification) is not sending acknowledgement the DELETE occurred or ODP.NET does not handle the message properly.
Can you check if the DB is sending the DELETE notification. You can query the CQ_NOTIFICATION$_DESCRIPTOR and CQ_NOTIFICATION$_TABLE tables. You will need to use a system admin account to query these tables. More info on these two tables are here:
https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_cqn.htm#ADFNS585
If your DELETE operation is not showing up here, then the problem is a DB bug. If not, then it's likely an ODP.NET bug.
-
Alex,
I can see CQ_NOTIFICATION$_DESCRIPTOR and CQ_NOTIFICATION$_TABLE in Public Synonyms, but when I attempt to query I get an error.
ORA-04044: procedure, function, package, or type is not allowed here
04044. 00000 - "procedure, function, package, or type is not allowed here"
I am logged in using the system admin account.
Just as a reminder I'm doing my testing on a XE 11.2 install. I don't know if that changes anything, but it's definitely not a production environment.
-
Well I upgraded to XE 18c and the change notifications worked as expected.
Regards,
-
Looks like this was an 11g DB bug.