How to use Oracle CHANGE NOTIFICATION Callback in .net application

How to use CHANGE NOTIFICATION Callback in .net application

1. You must give Grant permission on to your tables as shown below

grant change notification to database name

2. You have to registered your select query into database using your .net application as show below

below is code to register query

Public Sub ChangeNotification()
Try
Dim sql As String = "select firstname, lastname from person”

Dim constr As String = My.Settings.sConnectionString
Dim con As New OracleConnection(constr)

con.Open()

Dim cmd As New OracleCommand(sql, con)
Dim dep As New OracleDependency(cmd)
AddHandler dep.OnChange, AddressOf OnDatabaseNotification

cmd.ExecuteNonQuery()

While notificationReceived = False

Console.WriteLine("Waiting for notification...")
System.Threading.Thread.Sleep(2000)
End While

cmd.Dispose()
con.Dispose()

Console.WriteLine("Press ENTER to continue...")
Console.ReadLine()

Catch ex As Exception
Throw ex
End Try
End Sub

below is code to when data is updated or changed oracle database calls this event in your application

Public Sub OnDatabaseNotification(ByVal src As Object, ByVal args As OracleNotificationEventArgs)
Try
Console.WriteLine("Database Change Notification received!")
Dim changeDetails As DataTable = args.Details
Console.WriteLine("Resource {0} has changed.", changeDetails.Rows(0)("ResourceName"))

notificationReceived = True

Catch ex As Exception

Throw ex
End Try
End Sub

3. run this application and change/update your table information see this event called from oracle database

Enjoy !