All - I hope some of you can provide some design and implementation ideas for something that I am sure most of you have encountered at some point in time.
Objective:
Our Web application needs to *pull* data from a variety of Oracle databases located across our organization's Intranet. The pulls need to be manageable, preferably through the web interface, so that an administrator can start/run/stop different "jobs" to have a variety of information pulled back, in most cases saved to our own database, and also displayed in real-time (probably pulling from our local data, but not always).
System:
We have a
Java EE Web application built using
JBoss Seam, Hibernate/JPA,
EJB 3, and JSF/RichFaces. The data backend is Oracle 10gR2 and our app server is Oracle WebLogic 11g. Previously, we used straight Oracle database links and stored procedures + jobs to pull information and save it to our database. Although this is reliable, it doesn't leave much flexibility and we'd like to keep at a minimum things like stored procedures and Oracle jobs.
Current Ideas:
1) Create a simple framework to make
JDBC calls directly to these other data sources and pull/retrieve information through straight SQL queries.
2) Since we are using Seam and a managed persistence context, create additional persistence contexts hooked into several WebLogic data sources. Utilize Hibernate and SFSBs to pull information as needed.
3) Continue to use Oracle (PL/SQL) to pull and and store the appropriate information; the frontend will merely trigger backend stored procedures.
I'm very curious what other ideas that you all may have. Keep in mind that a major limitation here is that Web Services are simply not an option.
Thanks for your assistance.