I have a java application which is using Oracle SQLLDR utility to upload the CSV file data to the oracle database.
Occasionally, the SQLLDR utility doesn't provide the return/response code and whereas we could see the Index are disabled in the table (This ensures the SQLLDR utility is invoked) and also I have used TOP command in the server to find whether any SQLLDR process is running, but there are no such process.
In addition the DBA confirms, there is no active session on the database related to SQLLDR operation.
Is there anything which needs to be checked in the oracle table level ? Please let me know the way forward.
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
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.