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.
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).
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.
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.
How much data are you talking about? I ask because I would go a different approach based on the answer. If a ton of data, Oracle jobs or exports are likely better than raw JDBC. If only to minimize the network traffic.