Using JDBC versus Stored Procedures

[Robert Russell]

Hello - I am currently in a dilema, and don't have much time to benchmark different approaches.
Here is my problem:
I am reading in a flat file (approximately 100,000 records) into my Java application, processing this file (performing specific business logic) and then need to update my Oracle database with these records.
Unfortunately, I have many foreign keys set up in Oracle on the tables that I need to update/insert, so I would need to check these keys before performing an Insert/Update (Many JDBC calls).
My first approach was to write these records from Java to a staging table and having stored procedure perform the check on the constraints before performing the Insert/Update.
Is this the best approach? Should I keep all inserts/updates and constraint checks in Java??
I NEED HELP!!!
Thank you to everyone!

[Gregg Bolinger]

In my opinion, as many checks as you can do inside your java code the better. Simply because you are saving possbile Connection creation time and network traffic.
The problem you should consider is will the database ever change to the extent that you would have to dig back into your code because the constraints have changed.
It's hard to find that happy medium.

[Avi Abrami]

Hi Robert,
Naturally, I don't know what processing you need to do on this flat file, but I just wanted to make you aware (in case you weren't) of the "sqlloader" utility that is part of the Oracle database installation. It is the fastest way to load data from a file into the Oracle database.
You can find more information on it from these Web sites:
http://technet.oracle.com
http://tahiti.oracle.com
http://metalink.oracle.com
http://asktom.oracle.com
Hope this helps.
Good Luck,
Avi.

[Lu Battist]

Robert,
My experience with doing constrainst and business logic checking in java with a large input file is that the program performace really suffers. You can be smart and use prepared statements and the like but it will still run somewhat slow as it is just such a vast number of statements to send to the database and wait for your results to come back. That said, I still go with this option sometimes because its so much easier than writing the stored procedure. Just be sure the program has the time it needs.
In my humble opinion, the best way to takle this is to store the inputs into a temp table and kick off a stored procedure. Make sure the temp table is mostly VARCHAR unless you want to do type and format checking up front. Then just the throw the file line by line into the temp table and let the stored procedure(s) take care of the rest. You're stored procedure should be able to output any errors/bad records to a file or another temp table.

[Rafa Moreno]

Robert,
If you are using version 9, is posible to use external tables, so your file acts as an table inside the database.