loading csv file data into oracle database

Question

I am trying to load a csv file in oracle database using sql loader through java program. I have successfully executed it by run command, but i want to load csv file data in database through a java program. My programs are:

1. loadCsv.csv:

   ID,firstName,LastName,Address
   1,aditya,kumar,gaya
   2,abhijeet,chanda,kol
   3,Rahul,Jordar,kol
   

2. trial.ctl:

   LOAD DATA
   INFILE loadCsv.csv
   BADFILE trial.bad
   DISCARDFILE trial.dsc
   APPEND 
   INTO TABLE load1
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "”"
   (x,y,z,t)
   

3. SqlLoaderTest.java:

   public class SqlLoaderTest
   {
       public static void main(String[] args) {
           try {
               String sqlldrCmd = "sqlldr control=E:\\load_data\\trial.ctl"+
                  "LOG=trial.log "+
                  "DATA=E:\\load_data\\loadCsv.csv USERID=vehere/adi"+
                  "BAD=E:\\load_data\\trial.bad";
               System.out.println("SQLLDR Started ....... ");
               Runtime rt = Runtime.getRuntime();
               Process proc = rt.exec(sqlldrCmd);
               System.out.println("SQLLDR Ended ........  ");
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   }
   

It is compiled and run successfully but not inserting any data in database. your suggestion is highly appreciated.Thanx in advance.

Answer 1

I'm afraid, it does not run successfully, you just do not know what the error is.

Note that, if the sqlldr is executed, but fails do load the file into DB, you will not get any Java excaptions. Instead, the Process will return a non-zero exitValue, and there will probably be some output on the process' console.

What you need to do is to take the InputStream of your Process, and log it somehow (for example, dump it into the console), so you can see what the sqlldr output is. Alternatively, redirect output of sqlldr to a file in your sqlldrCmd string.

Answer 2

I got my error. The right code is:

  public class SqlLoaderTest
  {
  public static void main(String[] args) {
    try {
        String sqlldrCmd = "sqlldr username/pwd, control=trial.ctl";

        System.out.println("SQLLDR Started ....... ");
        Runtime rt = Runtime.getRuntime();
        Process proc = rt.exec(sqlldrCmd);
        System.out.println("SQLLDR Ended ........  ");
      } catch (Exception e) {
        e.printStackTrace();
    }
   }
  }