Re: SQL or Java?

Reasonable people may disagree. But if the point is speed and data integrity do the following:

1. Load the file into a quarantine table in a quarantine schema using direct path SQL Loader
2. Use a PL/SQL procedure, or package, to validate the data and move it to the final table(s) in the application schema

Other comments:
1. Get Java as far away from this as you can. 2. Have someone evaluate the competency of your DBA.

Daniel Morgan

AmoroSSo wrote:

> 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 - 13:42:29 CET