SQL
connect scott/tiger;
create table employee
(
id integer,
name varchar2(10),
dept varchar2(15),
salary integer,
hiredon date
)
Control file
load data
infile '/home/db1212/x.txt'
into table employee
fields terminated by ","
( id, name, dept, salary )
x.txt
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400
Execute
$ sqlldr scott/tiger control=/home/db1212/x.ctl
returns
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Oct 17 21:23:47 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 5
Table EMPLOYEE:
5 Rows successfully loaded.
Check the log file:
x.log
for more information about the load.
Execute second time to generate error
$ sqlldr scott/tiger control=/home/db1212/x.ctl
returns
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Oct 17 21:25:39 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
SQL*Loader-601: For INSERT option, table must be empty. Error on table EMPLOYEE
Truncate table in SQL*Plus by
truncate table employee;
Using following Java class from inside
import java.io.BufferedReader;
import java.io.InputStreamReader;
public class t1 {
public static void main(String[] args) {
t1 obj = new t1();
String output = obj.executeCommand();
System.out.println(output);
}
private String executeCommand() {
StringBuffer output = new StringBuffer();
try {
Process p = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c", "sqlldr scott/tiger control=/home/db1212/x.ctl"});
p.waitFor();
BufferedReader reader
= new BufferedReader(new InputStreamReader(p.getInputStream()));
String line = "";
System.out.println("Return code:"+p.exitValue()+"\n");
while ((line = reader.readLine()) != null) {
output.append(line + "\n");
}
} catch (Exception e) {
e.printStackTrace();
}
return output.toString();
}
}
Build and run t1.java
$ javac t1.java
$ java t1
returns
Return code:0
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Oct 17 21:30:31 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 5
Table EMPLOYEE:
5 Rows successfully loaded.
Check the log file:
x.log
for more information about the load.
Executing second time to mimic error
$ java t1
returns
Return code:1
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Oct 17 21:30:39 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
truncating table again
truncate table employee;
and changing the input file x.txt
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,MarketingAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA,9500
500,Randy,Technology,6000
A501,Ritu,Accounting,5400
gives for execution
$ java t1
following output
Return code:2
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Oct 17 21:47:05 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 5
Table EMPLOYEE:
3 Rows successfully loaded.
Check the log file:
x.log
for more information about the load.
This means:
So in case of
- succesful execution EX_SUCC = 0
- general SQLLoader error like "SQLLoader-601: For INSERT option, table must be empty. Error on table EMPLOYEE" i.e.unsuccesful execution or parameter gives EX_FAIL = 1 (Unix, Windows returns 3)
- succesful execution / loading but with SQL Errors like "ORA-12899: value too large for column "SCOTT"."EMPLOYEE"."DEPT" (actual: 44, maximum: 15)" returns EX_WARN = 2
Unfortunately documentation states
SQLLoader returns any exit code other than zero, you should consult your system log files and SQLLoader log files for more detailed diagnostic information.
which means nothing else then that there is no way to get the errors directly as stderr, pipes etc. and you have to verify the written log file if EX_FAIL or EX_WARN.
