关于用sqlldr导入日期的问题

chencl2bj 2011-01-30 10:27:40

我今天利用sqlldr进行数据输入在输入一个date类型的数据时发生错误,诚心求解错误原因和解决方法
我的控制文件是
LOAD DATA
INFILE 'emp.dat'
replace
INTO TABLE emp when empno != ''
(Empno position(1:4) integer external,
ename position(9:13) char,
Hiredate position(18:28) Date,
Job position(33:41) char,
Mgrno position(49:52) integer external,
Salary position(57:65) decimal external,
comm position(73:75) decimal external,
Deptno position(81:82) integer external)
数据为
1000 smith 01-Jan-1977 clerk 2000 12345.67 200 10
1010 jones 02-Jan-1977 mgr 2000 212345.67 200 10
1020 manus 01-Jan-1988 test 2000 12345.67 200 10
1030 tanus 01-Jan-1997 math 1080 12345.67 200 20
1040 smath 01-Jan-2007 clerk 1080 12345.67 200 20
1050 sith 01-Jan-2008 clerk 2000 12345.67 200 10
1060 fith 01-Jan-1990 clerk 2000 12345.67 200 30
1070 lith 12-Jan-1991 spvsr 2000 12345.67 200 30
1080 rith 01-Jan-2001 clerk 2000 12345.67 200 30
2000 keith 01-Jan-1977 clerk 3000 12345.67 200 20
3000 tony 01-Jan-1977 president 12345.67 200 10

结果发生了错误错误的log文件是
SQL*Loader: Release 11.1.0.6.0 - Production on Sat Jan 29 21:27:23 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Control File: emp.ctl
Data File: emp.dat
Bad File: emp.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table EMP, loaded when EMPNO != BLANKS
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
ENAME 9:13 5 CHARACTER
HIREDATE 18:28 11 DATE DD-MON-RR
JOB 33:41 9 CHARACTER
MGRNO 49:52 4 CHARACTER
SALARY 57:65 9 CHARACTER
COMM 73:75 3 CHARACTER
DEPTNO 81:82 2 CHARACTER

Record 1: Rejected - Error on table EMP, column HIREDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 2: Rejected - Error on table EMP, column HIREDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 3: Rejected - Error on table EMP, column HIREDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 4: Rejected - Error on table EMP, column HIREDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 5: Rejected - Error on table EMP, column HIREDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 6: Rejected - Error on table EMP, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 7: Rejected - Error on table EMP, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 8: Rejected - Error on table EMP, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 9: Rejected - Error on table EMP, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 10: Rejected - Error on table EMP, column HIREDATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 11: Rejected - Error on table EMP, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Table EMP:
0 Rows successfully loaded.
11 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 4352 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 11
Total logical records rejected: 11
Total logical records discarded: 0

Run began on Sat Jan 29 21:27:23 2011
Run ended on Sat Jan 29 21:27:23 2011

Elapsed time was: 00:00:00.14

...全文