SQL or Java?

I am working in a project were we need to move a lot of data (100 Million records a year) into Oracle (8i).
We have a Java program (EJB) that does the file and record integrity checks and loads the data into three Oracle tables.

Then we run a set of SQL stored procedures to check the integrity and validity of the individual fields and store the results in yet another table. We believe this process could not be done with the original files, since they are just plain text files.

The forth table is then use to create a report that will help the people that submitted the data for cleaning purposes, so they can send it again (and re-load it into the database). This process goes on until the data is as clean as possible. At that point the data in the original three tables is moved and converted to another database that will be used for reporting.

This other database is normalized and read only.

The DBA is telling us that we shouldn't use Oracle to do this checking (nor the moving to the final database), but an external application like Java or C++. Our opinion is that it would be faster, and less intensive for the network and the database, to do it in Oracle using PL/SQL.

So the question is ... who is right? or how can I find information or benchmarks on this topic.

Thanks in advance,

Paco Morales (francisco_at_ilore.com) Received on Wed Jan 16 2002 - 14:27:26 CST