This post is a duplicate from the "..Framework" forum. I dint get any answers their, so thought may be here it will catch somebody's attention. Sorry about that. The moderators may kindly transfer this question to the appropriate forum and delete from the rest.
The environment is as follows : Weblogic 9.2, Oracle 10g 1 Admin server, 3 App servers round robin
Scenario : ETL/Informatica extract customer data from source system and populate our database. We need to analyze this data and accordingly send Emails to the customers. This means checking the "status" column in some table and then invoke the Email module.
Solution1 : Use a scheduler like Quartz that keeps polling the status column in the said table and accordingly invokes the Email module. Pros: More robust than the other 2 solutions (ok, I feel its more robust). Cons: The Email sending is not real time.
Solution 2: Let ETL send messages to a JMS queue and a MDB pick up those messages and invoke the Email Module. Pros: Real-time Email sending. Cons: Issues like failed messages, then re-trying failed messages by publishing to a failure queue etc. In a distributed system this is even more painful.
Solution 3: Whenever the status column in the said table is populated with the desired value, invoke a DB trigger that in turn invokes a "Java Stored Procedure " that ultimately calls the E-mail module. Pros: Real Time Email sending. Cons: I don't know. I am just told by every other person that Java Stored procedures are a nightmare to work with. Have tried once before, and except for the fact that Oracle was using a old JVM in that particular version of DataBase, there were not many problems.This is 10g , so things should be better...or so I think.
I am inclined for Solution1 but my manager is inclined on Solution2.