How to poll an Oracle Database for insert/updates?

, ,
1

The following integration has to be implemented in my Integration Server (IS):

Every time an insert or update operation is performed in an Oracle Database, the changes have to be
reflected in an Oracle Applications v11. I have the Oracle Applications and Oracle Database Adapters already
installed in the IS.

Is there any way to monitor the oracle database in order to notify (i.e., the IServer got to know that some change occurred in the Orac DB) that an insert or update operation
was performed without using a Publishing-and-Subscribe Model?

If I decide to use the Publishing-and-Subscribe Model, is a JDBC adapter required to do so?

The Publishing-and-Subscribe Model is the best way to implement this notification service?

2

For wM 6, use the notification features of the DB adapter.

For pre-6 you can easily implement a facility.

Create a buffer table that can hold key information about changes in the table(s) of interest (e.g. insert/update/delete op, key info, etc.)

Create a trigger on the table(s) of interest. The trigger will add an entry to the buffer table.

Create a scheduled task on IS. This task will poll the buffer table to see if there is any work to do. If there are entries in the table, then do the necessary work.

That’s the basic approach. You may need to add some facilities to guard against processing the same row more than once in the case of a failure mid-stream.

HTH.

3

Is the DB Adapter that you mentioned a JDBC Adapter?

The JDBC Adapter supports adapter notification. But my Company didn’t purchase this adapter.

Can I implement this notification service having only the Oracle Database Adapter?

Thanks in advance,
M�nica

4

I am assuming that when you talk about Oracle DB Adapter in IS 6.0.1 you are talking about the ‘WmDB’ package. The only Oracle DB Adapter i am aware of is the ‘Enterprise’ Oracle Adapter, Anyway one way for achieving database notifications (polling for changes) has already been discussed in this Thread.

You can also write a Java Stored Procedure in Oracle that will invoke an IS services using the webM client API or simple http post. This stored procedure can be triggered by an insert/update in Oracle.