Saturday, January 23, 2016

Database Change Notification Listener Implementation

Oracle DB could notify client, when table data changes. Data could be changed by third party process or by different client session. Notification implementation is important, when we want to inform client about changes in the data, without manual re-query. I had a post about ADS (Active Data Service), where notifications were received from DB through change notification listener - Practical Example for ADF Active Data Service. Now I would like to focus on change notification listener, because it can be used in ADF not only with ADS, but also with WebSockets. ADF BC is using change notification to reload VO, when configured for auto refresh - Auto Refresh for ADF BC Cached LOV.

Change notification is iniatilized by DB and handled by JDBC listener. There are two important steps for such listener - start and stop. Sample application -, implements servlet DBNotificationManagerImpl:

I'm starting and stopping DB change listener in servlet init and destroy methods:

DB listener initialization code is straightforward. Data changes are handled in onDatabaseChangeNotification method. Listener is registered againt DB table executed in initial query:

In my example I'm listening for changes in Employees table. Each time, when change happens - query is re-executed to calculate new average salary values for jobs:

On first load, when servlet is initialized - listener is registered. Initial data read happens and listener is reported as started:

I can change value in DB, commit it:

DB change listener is notified and query is being executed to fetch new averages. Value for SA_REP is changed:

I hope it makes it more clear how to use DB change listener. In my future posts I will describe how to push changes to JET through WebSocket.


Mohamed Anwar said...

Beautiful :)

Luc Bors said...

Hi Andrejus,
I have been using this many times and it works great in DB Versions before 12.1.
However, when you are using 12.1.x you will run into an ORA-65131 "Feature Continuous Query Delivery is not supported in a pluggable DB"

My sources say that DB Change Notification is one of the features that is not yet fully functional in 12.1.x.
This is caused by the introduction of the Multitenant Architecture. Fixing the this seems to have low priority.
If you have any other information/fixes or workarounds, please feel free to share them



Andrejus Baranovskis said...

Hi Luc,

Thanks a lot for follow up. I will keep this in mind and will share workaround (if any), in the future.