wood burning stoves*
The moose likes JDBC and the fly likes Writing many records to an Oracle table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Writing many records to an Oracle table" Watch "Writing many records to an Oracle table" New topic
Author

Writing many records to an Oracle table

Kelvin Lee
Greenhorn

Joined: Jun 05, 2006
Posts: 1
Hi all,

My Java web application has to parse a large data file of 500,000 records. These records will be stored in an Oracle 9i database table. I wonder what would be the efficient way to write data to the table.

I tried to write about 1,000 records to the table in one go, but the table seems to be unusable now (Error: java.sql.SQLException: Protocol violation, SQL State: null, Error Code: 17401).

Your help is greatly appreciated.

Kelvin
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
Kelvin, my thought is loading .5 million through Java code is not great idea. Did you look at other ways to load (like using sqlldr). If you have constraint that Java prepares these data [clean,transform] then you can write to a File stream and invoke a script which performs direct load to oracle.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Purushothaman Thambu:
5 million through Java code is not great idea. Did you look at other ways to load (like using sqlldr).


I will also recommend SQL Loader.

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Writing many records to an Oracle table