Help: Executing sqlldr.exe in java.

[thg]

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'
)

[shreyasgore]

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);

}
}

[davey23uk]

9 years old

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

[john415]

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