Question Best practices for Oracle database integration

, , ,
1

I need to catch changes to an Oracle database. Changes in one table are fine, you use a notifier adapter. But if you need to get more data from the database than just from the one table…

When the Address table is updated, I need to retrieve some corresponding information from the Person table before publishing the change document. As far as I can see, there are 3 options:

  1. Place notifiers on the Address table. Subscribe to the document this produces. In the invoked service, utilize select adapters to get the additional information needed before publishing the “final” document.

  2. Place notifiers on the Address Table. Subscribe to the document this produces. In the invoked service, call a stored procedure in the database which returns the information I want and publish.

  3. Create a new table in the database containing fields for the data that need to be published. Create a stored procedure that is triggered by changes to the Address table, collects the needed information and puts it in this table for a short duration. Place insert notifier on this table and subscribe to this “final” document.

I can see a possible performance issue with the “ping-pong” traffic between webMethods and Oracle in solution 1, and solution 3 creates two “temporary” tables where one is sufficient, strictly speaking.

Does anyone have views on what would be the best practice here, or a different and better solution?

Cheers, Brigt

2

Hi, Brigt:

The first option is one most used. Notification document sends out the key fields which has a very small size. Then, use SELECT operation to retreive all the data you want from different tables 9not only two). After having them, publish map them to the " final" document and deliver or publish.

This approach is used not only for Oracle DB, but also for other CRM, Billing applications.

I hope this can help you.

3

I concur with ZGW. Option 1 is quite common, along with ZGW’s clarification that the notification event contains key information only. This is how the now-deprecated ATC worked.

4

I think I’ll stick with option 1 then. I won’t have to brush up on my PL/SQL skills either ;). Thanks for the quick response!