Get notified when loading of data is done by sqlloader

Question

I have a java code through which I call the bat file. This bat file contains call to sqlldr which loads data from a txt file into a table(in oracle). This is the java code to call the bat file

ProcessBuilder pb = new ProcessBuilder(new File(".").getAbsolutePath()
                + File.separator + "ld.bat");
        System.out.println("Before start");
Process start;
            try {
                start = pb.start();


        } catch (IOException e) {

            e.printStackTrace();
        }

Below this code I am creating new threads and doing my other tasks. The loading of data takes place in the background. I want to do some task (e.g moving the txt data file to another location) once loading is done. Is there a way to get notified once the loading of data is done by sqlldr. I have come across a work around - I can keep checking the number of rows in d table and compare it with the number of rows in the raw txt file. But I would like to know if there is some another solution for this. Also I dont want to use start.waitFor() as this will keep my other activities on hold.

score 1 · Answer 1 · answered May 25 '12 at 08:23

1

I'm not sure I entirely understand your question - is there any reason you cannot put the call of the batch script into a new Thread?

e.g.

new Thread("Load Data"){
  @Override
  public void run() {
    // load data
    // move txt data file
  }
}.start();

// do other activities 
// (Presumably they're not dependent on the data having been loaded...)

answered May 25 '12 at 08:23

amaidment

6,942
5
52
88

I made a new thread, but now when I give start.waitFor() it keeps on waiting after inserting 384 rows. The file contains 996 rows. All the remaining rows get inserted only after I stop the JVM. – rdp May 25 '12 at 09:28
@dilip - I assume you're doing an insert per row. You should batch this up, and you'll get better performance and it'll work as a single transaction. e.g. `INSERT INTO table (columns...) VALUES (row1),(row2),(row3)` – amaidment May 25 '12 at 09:33
My batch script contains sqlldr command. ie sqlldr username/password control=control_file.ctl and the control file contains basic load data command. – rdp May 25 '12 at 09:46

score 0 · Answer 2 · edited Oct 05 '13 at 22:42

I have the same problem, and solved like this:

 public void runSqlldr() throws IOException, Exception {
        String sqlldrRunCmd = StartParserEngineHW3G2G.SQLLDRPATH
                + " userid='" + DBHelper.username + "/" + DBHelper.password + "@" + StartParserEngineHW3G2G.TNSNAME + "'"
                + " control='" + this.ctlFile.getAbsolutePath() + "'"
                + " log='" + this.logFile.getAbsolutePath() + "'"
                + " direct=true rows=1000000 errors=1000000000";
    Runtime rt = Runtime.getRuntime();
    Process proc = rt.exec(sqlldrRunCmd);

    while(true){
        if(logFile.exists() && logFile.length() > 0){
            System.out.println(logFile + " is exist and sqlldr completed and lentgh is " + logFile.length());
            proc.destroy();
            break;
        }
        else{
            System.out.println(logFile + " is not exist, and waiting and logfile length is " + logFile.length());
            Thread.sleep(3000);
        }

    }
}

score 0 · Answer 3 · answered Oct 05 '13 at 23:32

0

This is the exact solution, already tested.

When sqlldr started, below code waits till it ends, then execute "some business"

    Runtime rt = Runtime.getRuntime();
    Process proc = rt.exec(sqlldrRunCmd);
    int exitVal = proc.waitFor();
    System.out.println("Process exitValue: " + exitVal);

    // some business in here

answered Oct 05 '13 at 23:32

Remzi OZCAN

1

This answer does not help me as I have mentioned in the question that I dont want to use waitFor() as it has other drawbacks. – rdp Oct 10 '13 at 14:26

Get notified when loading of data is done by sqlloader

3 Answers3