Oracle SQLLDR utility no response

Question

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.

Answer 1

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.