Change Notification Clarification: OracleNotificationEventArgs Doesn't Include Delete When Updates o — oracle-tech

    Forum Stats

  • 3,716,128 Users
  • 2,242,960 Discussions
  • 7,845,836 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Change Notification Clarification: OracleNotificationEventArgs Doesn't Include Delete When Updates o

4186764
4186764 Member Posts: 4
edited February 2020 in ODP.NET

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

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited February 2020

    Is the DB itself sending a DELETE notification? If so, does the ODP.NET trace show that it receives a DELETE notification?

  • 4186764
    4186764 Member Posts: 4
    edited February 2020

    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

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited February 2020

    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.

  • 4186764
    4186764 Member Posts: 4
    edited February 2020

    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.

  • 4186764
    4186764 Member Posts: 4
    edited February 2020 Accepted Answer

    Well I upgraded to XE 18c and the change notifications worked as expected.

    Regards,

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited February 2020

    Looks like this was an 11g DB bug.

Sign In or Register to comment.