Wednesday, 27 April 2011

Java: Loading data from file into a Oracle database

It's possible to do using Sql Loader.SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

Here are the steps you should implement:

1) Intall oracle db, full or express

2) Create files

*.txt file with data (may be any extension) and *.ctl file, it's using for specify mapping with data in file and database fields

example test.txt :
Bob     12 1999
George  9  1977

example test.ctl:
-- loading data
infile *
  persone_name POSITION(1:8) CHAR ,     
 persone_month POSITION(8:9) CHAR,
 persone_year POSITION(12:14) CHAR,
In place of Replace you can use also APPEND - if data doesn't already exist,INSERT - it requires the table to be empty

3) Execute in the console
String command = "sqlldr userid=" + "oracleuser/oraclepassword" + " control=" + "test.ctl"+ " data=" + "test.txt";
Process process = Runtime.getRuntime().exec(command);
int exitVal = process.waitFor();

exitVal may be:
0 - successful
1 - failed
2 - warn
3 - fatal

Oracle - SQL*Loader Control File Reference
Some examples

No comments:

Post a Comment