We have a requirement of having an Insert adapter notification from a table which is of Type: Synonym. After adding the notification and while enabling the polling notification we noticed below error.
[ART.116.3038] Adapter Runtime (Notification): Error in Notification Callback:enableCallback notification test1:segment_test. [ADA.1.349] Execution of DDL query of Notification failed during enable/disable. Please check the datatypes of columns selected in notification. [SoftwareAG][Oracle JDBC Driver][Oracle]ORA-25001: cannot create this trigger type on this type of view.
Latest fix: JDBC_9.10_Fix13 is applied
Driver: com.wm.dd.jdbcx.oracle.OracleDataSource
driverType=thin
connectionproperties={includeSynonyms=true}
We added the connection properties as well and tried enabling but the issue still persists. Is our adapter supports Insert notification on Synonym table type? Do i have to modify any other parameter to make it working or do i have to request clients for the direct DB table access instead of a Synonym?
You would want to check with the DBA, to understand if there is any authority issue on the synonym table that has been created - generating this error.
Essentially by creating Insert notification, you are monitoring the table for a changes of Insert type (essentially a trigger). If that is allowed on these synonyms or not.
Trust the error message - [SoftwareAG][Oracle JDBC Driver][Oracle]ORA-25001: cannot create this trigger type on this type of view.
Based upon this, the synonym used is for a DB view.
A search for the error shows several results including one site that notes: “The Oracle documentation notes that you cannot create a trigger on a view unless it is an instead of trigger”.
My guess is that the adapter notification cannot be set up to create this type of trigger.
I would recommend using only basic notification types. Using the “operation-specific” types like insert, update, etc. requires the DB user have DDL permissions on the DB and schema, which sometimes is frowned upon. Plus, and this is the bigger issue, intentionally/accidentally disabling the notification destroys the DB trigger – meaning you will miss any events while the trigger is disabled, which is most likely undesired.
Using basic notification requires that the trigger and buffer table be created manually, but that is a one-time activity. Then the basic notification can be disabled/enabled at will without disturbing the DB trigger and buffer table at all. And the DB user does not need “excessive” permissions for creating/dropping DB objects.