load csv file to oracle database

Question

Hi I wanted to load csv file in Oracle database using java but what I am getting error like "ora-00900 invalid sql statement". I am using oracle Database 11g Enterprise Edition. So I don't understand why it doesn't accept my load statement. Any help? Thanks in advance.

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class test {
public static void main(String[] args){
  test t=new test();
  t.inserintoDb("C:\\Users\\fiels\\2.csv");
}
public void inserintoDb(String path){
Connection conn=null;
Statement stmt=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=(Connection) DriverManager.getConnection(
       "jdbc:oracle:thin:@address:orcl","user","password");

stmt=conn.createStatement();
String select1="truncate table table1";
stmt.execute(select1);
String select2="LOAD DATA INFILE'" +path+"' INTO TABLE table1 FIELDS TERMINATED BY ',' (customer_nbr, nbr)";
stmt.execute(select2);
}catch(Exception e){
 e.printStackTrace();
 }

}
}

Answer 1

Does infile works on Oracle? It seems that only on MySql.. The SQL Loader alternative is really fast. Check the official documentation to see how to config it:

As the question states that you want to use Java here is the help for calling the SQL Loader from Java. It bassically uses a Runtime but depends on the operating system:

String[] stringCommand = { "bash", "-c", "/usr/bin/sqlldr username/password@sid control=/path/to/sample.ctl"};

Runtime rt = Runtime.getRuntime();
Process proc = null;
try {
    proc = rt.exec(stringCommand);
}catch (Exception e) {
    // TODO something
}finally {
    proc.destroy();
}

But if you just want to load some table for your personal use you wont need java. You can call it from a .bat file.