我有一个java应用程序,它使用Oracle SQLLDR实用程序将CSV文件数据上载到oracle数据库。
有时,SQLLDR return/response实用程序不提供return/response代码,尽管我们可以看到表中禁用了索引(这确保了调用SQLLDR实用程序),而且我还在服务器中使用了TOP命令来查找是否有SQLLDR进程正在运行,但没有这样的进程。
此外,DBA确认,数据库上没有与SQLLDR操作相关的活动会话。
在oracle表级别有什么需要检查的吗?请让我知道前进的路。
发布于 2020-10-17 19:57:08
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.java
$ 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.这意味着:
所以,如果
。
不幸的是,文档声明
SQL_Loader返回除零以外的任何退出代码,有关更详细的诊断信息,请参阅系统日志文件和SQL_Loader日志文件。
这意味着没有其他任何方法可以直接获得错误,如stderr、管道等,如果是EX_FAIL或EX_WARN,则必须验证所编写的日志文件。
https://stackoverflow.com/questions/62566525
复制相似问题