I am trying to implement a system that detects modifications (e.g. insert operation) in the database. I searched over net and found these two examples: [amis technology blog|http://technology.amis.nl/2012/06/18/notifying-adf-applications-of-database-changes-fast-and-lean-using-database-query-result-change-notification-part-one/] and [niallcblogs post |http://niallcblogs.blogspot.nl/2009/06/auto-refreshing-adf-chart-objects-in.html]
I have downloaded a sample application in AMIS' BLOG and follow the steps which registers notifications listeners to database.
Both requires "change notification" privilege in database so I have added change notification privilege to my db user.
When I run the application It registers listeners to db notifications correctly with "registerForDatabaseNotifications" method.
Adds DatabaseNotificationHandler instances as listeners. But when I inserted a row to the table,
It does not reach to "onDatabaseChangeNotification" method as expected in the example.
After this unsuccessful attempt, I ve tried niallcblogs post, followed the steps to create and refresh a chart according to emp and dept
tables iln SCOTT schema. But again the notification does not reach to the method.
I use Jdeveloper 126.96.36.199.0 and access to HR schema over a remote connection to our Oracle DB
Do you have any idea? I can add project sources if needed?
I checked my db version with the sql
select * from v$version;
The version of db is "*Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit Production*"
The document you sent says the database must be at least 11.0.0. So the problem must be something different.
Do you have any other suggestion?
well, if you followed sample from amis blog, then there is db_notifications table and db trigger.
I suppose that you already checked if this table contains new records, so if this is ok and your registration query is also ok, I don't have other suggestions :(
Yes I followed the steps in the sample and created the trigger and db_notifications table.
It generates new records in the table db_notifications. The query registration process is being passed
without any exception. The only problem is DB does not notify the application over jdbc. I am running the application
on my local weblogic server. Only the db is remote. Does it cause any problem ?
Maybe you can try some simple application, for example, you can adjust this cmd line program: http://docs.oracle.com/cd/E14072_01/java.112/e10589/dbchgnf.htm#CHDCJFGJ
and if this works then concentrate to problem in ADF.
I have tried the example you sent but it does not work again
It logs only the rows:
-HR.DEPT is part of the registration.
-inserted one row with ROWID=AAB/EVAAEAAAO/OAAC
-inserted one row with ROWID=AAB/EVAAEAAAO/OAAD
but no other attempt to handle the event.
The problem remains unsolved. Do you have any other suggestion for me?
Edited by: sgoker on Nov 21, 2012 1:25 AM
Well, at least you know that this is not problem in ADF :)
Try to set prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, *"false"* );
This will listen to all changes on your table.
If this works, then problem is with registration query, and if don't work then try to disable all firewalls, antiviruses etc. :)
I just tested this standalone sample.
- Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production
- JDev 220.127.116.11
- DB is on remote host
- Standalone app is in WinXP VM on Win7 host
Because my VM is behind NAT, I configured port forwarding, and set:
prop.setProperty(OracleConnection.NTF_LOCAL_HOST,"my vm ip address");
prop.setProperty(OracleConnection.NTF_LOCAL_TCP_PORT, "forwarded port");
This didn't work until I disabled firewalls.