Help: Executing sqlldr.exe in java.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Help: Executing sqlldr.exe in java.

  1. #1
    Join Date
    Jan 2001
    Posts
    59

    Lightbulb


    hi experts,

    my sql loader command works fine at the command prompt, but how can execute it in my java code?


    I have written a piece of code to execute windows commands in java. It works fine with all other commands such as "copy", "delete", and also SQL's bulk copy.... but just not the sql loader..... could anyone give me any hints?

    thanks.


    Here is my java code for executing windows commands:
    ----------------------------------------------------------------

    public static String runBCP(String cmd) {
    String str = "";
    try {
    System.out.println("\nEntering runBCP() ......");
    Process process_bcp = Runtime.getRuntime().exec(cmd);
    process_bcp.waitFor();
    int returnValue = process_bcp.exitValue();

    System.out.println("\nin runBCP(), returnValue = " +returnValue);
    if(returnValue != 0) {
    InputStream in = process_bcp.getInputStream();
    InputStreamReader preader = new InputStreamReader(in);
    BufferedReader breader= new BufferedReader(preader);
    String msg = null;
    while((msg = breader.readLine()) != null) {
    System.out.println(msg);
    str += "" + msg + "
    ";
    }
    System.out.flush();
    preader.close();
    breader.close();
    in.close();

    InputStream inError = process_bcp.getErrorStream();
    InputStreamReader preaderError = new InputStreamReader(inError);
    BufferedReader breaderError= new BufferedReader(preaderError);

    String errorMsg = null;
    while((errorMsg = breaderError.readLine()) != null) {
    System.out.println("Copy Error: " + errorMsg);
    str += "" + errorMsg + "
    ";
    }
    System.out.flush();
    preaderError.close();
    breaderError.close();
    inError.close();
    }
    else{

    InputStream in = process_bcp.getInputStream();
    InputStreamReader preader = new InputStreamReader(in);
    BufferedReader breader= new BufferedReader(preader);
    String msg = null;
    while((msg = breader.readLine()) != null) {
    System.out.println(msg);
    str += "" + msg + "
    ";
    }
    System.out.flush();
    preader.close();
    breader.close();
    in.close();
    }
    process_bcp.destroy();

    // System.out.println("IMPORT PORTFOLIO DATA DONE");
    }catch(InterruptedException e){
    System.out.println("Exception : " + e.toString());
    e.printStackTrace();
    str = "UPLOAD FAILED";
    }catch(Exception e){
    str = "UPLOAD FAILED";
    System.out.println("Exception : " + e.toString());
    e.printStackTrace();
    }
    return str;
    }
    ---------------------------------------------------------------

    and here is my command:

    sqlldr control=C:\codes\bcp\oracle\load.ctl, log=load.log, bad=load.bad, data=C:\codes\bcp\oracle\data.csv, userid=system/manager@RESEARCH

    ------- the data.csv -------

    1, 7.6, 23232.24, 9.6
    2, 8.3, 34353.53, 8.3
    3, 6.7, 55353.53, 8.6
    4, 9.4, 63472.53, 9.8
    5, 8.2, 44443.53, 8.1

    ---------- the control file, load.ctl ----

    LOAD DATA
    INFILE 'data.csv'
    REPLACE
    INTO TABLE hgantest
    (
    loan_num INTEGER EXTERNAL TERMINATED BY ',',
    none FILLER DECIMAL EXTERNAL TERMINATED BY ',',
    cur_bal DECIMAL EXTERNAL TERMINATED BY ',',
    interest DECIMAL EXTERNAL TERMINATED BY '\n'
    )



  2. #2
    Join Date
    Feb 2010
    Location
    India
    Posts
    1

    Resolution

    Hi
    Try the following code. The place where you call Runtime.getRuntime().exec (Pass the sqlldr command) which you have specified below your code. It worked for me.


    import java.io.BufferedReader;
    import java.io.InputStream;
    import java.io.InputStreamReader;

    class runBCP
    {
    public static void main(String cmd[]) {
    String str = "";
    try {
    System.out.println("\nEntering runBCP() ......");
    Process process_bcp = Runtime.getRuntime().exec("");
    process_bcp.waitFor();
    int returnValue = process_bcp.exitValue();

    System.out.println("\nin runBCP(), returnValue = " +returnValue);
    if(returnValue != 0) {
    InputStream in = process_bcp.getInputStream();
    InputStreamReader preader = new InputStreamReader(in);
    BufferedReader breader= new BufferedReader(preader);
    String msg = null;
    while((msg = breader.readLine()) != null) {
    System.out.println(msg);
    str += "" + msg + "";
    }
    System.out.flush();
    preader.close();
    breader.close();
    in.close();

    InputStream inError = process_bcp.getErrorStream();
    InputStreamReader preaderError = new InputStreamReader(inError);
    BufferedReader breaderError= new BufferedReader(preaderError);

    String errorMsg = null;
    while((errorMsg = breaderError.readLine()) != null) {
    System.out.println("Copy Error: " + errorMsg);
    str += "" + errorMsg + "";
    }
    System.out.flush();
    preaderError.close();
    breaderError.close();
    inError.close();
    }
    else{

    InputStream in = process_bcp.getInputStream();
    InputStreamReader preader = new InputStreamReader(in);
    BufferedReader breader= new BufferedReader(preader);
    String msg = null;
    while((msg = breader.readLine()) != null) {
    System.out.println(msg);
    str += "" + msg + "";
    }
    System.out.flush();
    preader.close();
    breader.close();
    in.close();
    }
    process_bcp.destroy();

    System.out.println("IMPORT PORTFOLIO DATA DONE");
    }catch(InterruptedException e){
    System.out.println("Exception : " + e.toString());
    e.printStackTrace();
    str = "UPLOAD FAILED";
    }catch(Exception e){
    str = "UPLOAD FAILED";
    System.out.println("Exception : " + e.toString());
    e.printStackTrace();
    }
    System.out.println(str);

    }
    }

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    9 years old

    9 YEARS! in a thread called forum archives, god damn some people are thick

  4. #4
    Join Date
    May 2011
    Posts
    1

    Sql loader

    i am trying to run the code but where to place the control file and data file in eclipse .
    as

    import java.io.BufferedReader;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.util.Scanner;

    class Test2
    {
    public static void main(String cmd[]) {
    String str = "";
    System.out.println("Enter command ");
    Scanner sc = new Scanner (System.in);
    String a=sc.next();
    try {
    System.out.println("\nEntering runBCP() ......");
    Process process_bcp = Runtime.getRuntime().exec(a);
    process_bcp.waitFor();
    int returnValue = process_bcp.exitValue();

    System.out.println("\nin runBCP(), returnValue = " +returnValue);
    if(returnValue != 0) {
    InputStream in = process_bcp.getInputStream();
    InputStreamReader preader = new InputStreamReader(in);
    BufferedReader breader= new BufferedReader(preader);
    String msg = null;
    while((msg = breader.readLine()) != null) {
    System.out.println(msg);
    str += "" + msg + "";
    }
    System.out.flush();
    preader.close();
    breader.close();
    in.close();

    InputStream inError = process_bcp.getErrorStream();
    InputStreamReader preaderError = new InputStreamReader(inError);
    BufferedReader breaderError= new BufferedReader(preaderError);

    String errorMsg = null;
    while((errorMsg = breaderError.readLine()) != null) {
    System.out.println("Copy Error: " + errorMsg);
    str += "" + errorMsg + "";
    }
    System.out.flush();
    preaderError.close();
    breaderError.close();
    inError.close();
    }
    else{

    InputStream in = process_bcp.getInputStream();
    InputStreamReader preader = new InputStreamReader(in);
    BufferedReader breader= new BufferedReader(preader);
    String msg = null;
    while((msg = breader.readLine()) != null) {
    System.out.println(msg);
    str += "" + msg + "";
    }
    System.out.flush();
    preader.close();
    breader.close();
    in.close();
    }
    process_bcp.destroy();

    System.out.println("IMPORT PORTFOLIO DATA DONE");
    }catch(InterruptedException e){
    System.out.println("Exception : " + e.toString());
    e.printStackTrace();
    str = "UPLOAD FAILED";
    }catch(Exception e){
    str = "UPLOAD FAILED";
    System.out.println("Exception : " + e.toString());
    e.printStackTrace();
    }
    System.out.println(str);

    }
    }



    when i enter by
    sqlldr userid=System/Violet415 control=loader1.ctl log=loader.log
    nothing comes up . please can you please help in this regards . Thank you in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width