使用sqlldr导入数据的是时候,在控制文件中去掉deptno列就可以正常导入。输入deptno列之后,所有的数据就都导入不进去,都跑到bad文件里面去了。我查过了没有空格之类的字符,请大神帮忙解释一下,谢谢!
load data
infile '/home/oracle/abC.csv'
truncate
into table t
fields terminated by ','
trailing nullcols
(EMPNO, ENAME,JOB,MGR,HIREDATE date 'YYYY/MM/DD HH24:MI:SS',SAL,comm,deptno)[oracle@oraclelinux2:/home/oracle]$cat ab.csv
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,1980/12/17 0:00,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20 0:00,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22 0:00,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2 0:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28 0:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1 0:00,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9 0:00,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19 0:00,3000,,20
7839,KING,PRESIDENT,,1981/11/17 0:00,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8 0:00,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23 0:00,1100,,20
7900,JAMES,CLERK,7698,1981/12/3 0:00,950,,30
7902,FORD,ANALYST,7566,1981/12/3 0:00,3000,,20
7934,MILLER,CLERK,7782,1982/1/23 0:00,1300,,10SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
load data
infile '/home/oracle/abC.csv'
truncate
into table t
fields terminated by ','
trailing nullcols
(EMPNO, ENAME,JOB,MGR,HIREDATE date 'YYYY/MM/DD HH24:MI:SS',SAL,comm,deptno)[oracle@oraclelinux2:/home/oracle]$cat ab.csv
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,1980/12/17 0:00,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20 0:00,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22 0:00,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2 0:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28 0:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1 0:00,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9 0:00,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19 0:00,3000,,20
7839,KING,PRESIDENT,,1981/11/17 0:00,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8 0:00,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23 0:00,1100,,20
7900,JAMES,CLERK,7698,1981/12/3 0:00,950,,30
7902,FORD,ANALYST,7566,1981/12/3 0:00,3000,,20
7934,MILLER,CLERK,7782,1982/1/23 0:00,1300,,10SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
你在linux下导入,回车和后面的数字连在一起导入了,因为连在一起不是数字,所以报错了。