http://www.csdn.net/expert/topic/841/841791.xml?temp=.9379541LOAD DATA
INFILE 'ulcase6.dat'
INSERT
INTO TABLE emp
SORTED INDEXES (empix)
(empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)LOAD DATA
INFILE 'ULCASE7.DAT'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
INFILE 'ulcase6.dat'
INSERT
INTO TABLE emp
SORTED INDEXES (empix)
(empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)LOAD DATA
INFILE 'ULCASE7.DAT'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE
)加上"TRAILING NULLCOLS"来指定不够长度的字段都设成null
我已经varchar2(2000)了,其他的是clob字段。谢谢
你把control文件和数据文件中出错的地方贴出来看看,可能不是varchar2字段的问题
记录 243: 被拒绝 - 表 "英文化学名" 的列 CHEMICALNAME 出现错误。
数据文件的字段超出最大长度该行数据如下(行中原本没有任何回车之类,回车是我现加的,分割符是#):
468#"1(2H)-Pyrimidineacetamide, N-(4-(((2,6-dimethylphenoxy)
acetyl)amino)-3-hydroxy-5-phenyl-1-(phenylmethyl)-pentyl)
tetrahydro-Alpha-(1-methylethyl)-2-oxo-, (1S-(1R*(R*),3R*,4R*))
- + 2,4,7,12-Tetraazatridecan-13-oic acid, 10-hydroxy-2-methyl-5-
(1-methylethyl)-1-(2-(1-methylethyl)-4-thiazolyl)
-3,6-dioxo-8,11-bis(phenylmethyl)-, 5-thiazolyl-methyl ester,
(5S-(5R*,8R*,10R*,11R*))- [CAS]"大概3百多的字符量。我的表设计中,第二个字段是varchar(2000)sql*plus版本:8.1.7.0.0