java Oracle SQLLDR实用程序无响应

Question

我有一个java应用程序，它使用OracleSQLLDR实用程序将CSV文件数据上载到Oracle数据库

偶尔，SQLLDR实用程序不提供返回/响应代码，而我们可以看到索引在表中被禁用（这确保了SQLLDR实用程序被调用），并且我在服务器中使用了TOP命令来查找是否有任何SQLLDR进程正在运行，但没有这样的过程

此外，DBA确认数据库上没有与SQLLDR操作相关的活动会话

在oracle表级别是否有需要检查的内容？ 请告诉我前进的方向

Answer 1

SQL

 connect scott/tiger;
 create table employee
(
  id integer,
  name varchar2(10),
  dept varchar2(15),
  salary integer,
  hiredon date
)

控制文件

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

执行

$ sqlldr scott/tiger control=/home/db1212/x.ctl

返回

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.

执行第二次以生成错误

$ sqlldr scott/tiger control=/home/db1212/x.ctl

返回

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

截断SQL*Plus中的表

truncate table employee;

从内部使用以下Java类

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

    }

}

构建并运行t1。爪哇

$ javac t1.java 
$ java t1

返回

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.

执行第二次模拟错误

$ java t1

返回

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

再次截断表

truncate table employee;

以及更改输入文件x.txt

200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,MarketingAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA,9500
500,Randy,Technology,6000
A501,Ritu,Accounting,5400 

行刑

$ java t1

跟踪输出

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.

这意味着：

所以如果

• 成功执行EX_such=0
• 常规SQL加载程序错误，如“SQLLoader-601:对于插入选项，表必须为空。表EMPLOYEE上的错误”，即执行不成功或参数给出EX_FAIL=1（Unix，Windows返回3）
• 成功执行/加载，但出现SQL错误，如“ORA-12899:列“SCOTT”的值太大”雇员”部门“（实际值：44，最大值：15）”返回EX_WARN=2

不幸的是，文件表明

SQLLoader returns any exit code other than zero, you should consult your system log files and SQLLoader log files for more detailed diagnostic information.

这意味着没有其他方法可以直接获取错误，如stderr、pipes等，如果exu失败或exu警告，您必须验证写入的日志文件