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 - WebSocketReusableApp.zip, 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.