importing a csv file into oracle database using java

Question

I am getting the following error

  Java.lang.ArrayIndexOutOfBoundsException: 0
  at oracle.jdbc.driver.OracleSql.main(OracleSql.java:1614)

I am using eclipse indigo and oracle 10g.how can i fix this problem.can anyone help me ?

i want to load a csv file into oracle database.. i've created a table named zt with three columns (id, s_date, data) CSV data contains as follows:

   a1,2015-04-15 17:40:20.0,18.5786    
   a2,2015-04-15 16:20:59.0,16.7868   
   a3,2015-03-15 16:20:59.0,16.51689    
   a4,2015-04-16 16:20:55.0,24.789028  
   a5,2015-02-15 17:55:59.0,28.784145

code

import java.io.FileReader;    
import java.sql.Connection;    
import java.sql.DriverManager;    
import java.sql.PreparedStatement;
import java.sql.Statement;
import au.com.bytecode.opencsv.CSVReader;

public class ImportTest {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        readCsv();
    }

    public static void readCsv() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn     =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xe","system","arry");
            PreparedStatement pstmt =conn.prepareStatement("Insert into zt values(?,?,?)");

            CSVReader reader = new CSVReader(new FileReader("D:\\zzzzzzzz.csv"), ','); 
            String[] nextLine;
            int i = 0;
            while((nextLine = reader.readNext()) != null) {
                i++;
                pstmt.setString(1,nextLine[0]);
                pstmt.setString(2,nextLine[1]);
                pstmt.setDouble(3,Double.parseDouble(nextLine[2]));
            }

            pstmt.close();
            conn.commit();
            conn.close();
        } catch(Exception e) {
            e.printStackTrace();       
        }
    }
}

Post full stack trace. but chances are that you are accessing an index in nextLine which does not exist. — Shahzeb
– Shahzeb, Commented May 4, 2015 at 6:19

Shahzeb · Accepted Answer · 2015-05-04 06:40:19Z

2

For starters you have

pstmt.setString(1,nextLine[0]);
pstmt.setString(1,nextLine[1]);
pstmt.setDouble(2,Double.parseDouble(nextLine[2]))

Note you have repeated parameter one twice and if exception is coming from database then this is very likely to be the cause.

more over

IndexOutOfBoundsException in Java is a runtime exception .As stated in the doc

Thrown to indicate that an index of some sort (such as to an array, to a string, or to a vector) is out of range. Applications can subclass this class to indicate similar exceptions.

It is obvious that you are accessing the index of the arrays without checking first. Always a dangerous thing to do.

I also agree it does seem the you CSV file is not correct but either way you problem is not limited to that and you should safeguard against it. I have added code to show a basic safeguard

Just put a check for length in your while loop like below

 while((nextLine = reader.readNext()) != null){
             i++;
                // Remember length 3 = index 2
                if (nextLine.length == 3){
                    pstmt.setString(1,nextLine[0]);
                    //I have changed it to 2 from 1 
                    pstmt.setString(2,nextLine[1]);
                    pstmt.setDouble(2,Double.parseDouble(nextLine[2]));
             }
    }

edited May 4, 2015 at 6:40

answered May 4, 2015 at 6:27

Shahzeb

4,7834 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ARoy Over a year ago

thanks for the solution. but getting one more error showing...... java.sql.SQLException: ORA-01843: not a valid month..... while creating a table s_date data type was taken as timestamp... pstmt.setString(2,nextLine[1]); how can i insert date with time in the table? @shahzeb

Rafal G. · Accepted Answer · 2015-05-04 06:22:35Z

2

That is quite simple. You are using CSVReader and giving it comma (',') as a field separator, however the 4 sample lines that you have showed us do not contain any commas.

Such a line 'a1 2015-04-15 17:40:20.0 18.5786' splitted on ',' will be just 1 string containing the entire line.

Your file is not a CSV strictly speaking.

answered May 4, 2015 at 6:22

Rafal G.

4,4401 gold badge29 silver badges42 bronze badges

1 Comment

ARoy Over a year ago

sorry.. i made some mistake while posting, now it's edited. thanks

Luke Woodward · Accepted Answer · 2015-05-04 08:31:21Z

It seems you're trying to run a class within the Oracle JDBC driver JAR rather than your code.

The Oracle JDBC JAR does contain a class oracle.jdbc.driver.OracleSql, and this class has a main method, so it's possible to run this class, but I don't think that's what you want to do.

Here's how to reproduce this error, give or take a different line number:

C:\>java -cp ojdbc14.jar oracle.jdbc.driver.OracleSql
java.lang.ArrayIndexOutOfBoundsException: 0
    at oracle.jdbc.driver.OracleSql.main(OracleSql.java:1717)

This is the full output: I haven't truncated the stacktrace.

I couldn't reproduce this behaviour with a version of the JDBC JAR newer than ojdbc14.jar: I got a usage message instead attempting the same thing with ojdbc5.jar, ojdbc6.jar and ojdbc7.jar. The exception stacktrace above suggests that ojdbc14.jar is failing to check the number of command-line arguments before attempting to use them, whereas later versions fix this defect.

Collectives™ on Stack Overflow

importing a csv file into oracle database using java

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related