使用external table 导入数据报错 1226 ,求解 !
create table aaa
(
a char(11),
b char(11),
c integer,
d integer,
e char(2),
f char(1),
g char(10),
h char(20),
i char(10),
j char(10),
k char(1),
l char(32),
m char(10),
n decimal(16,2),
o decimal(16,2),
p decimal(16,2),
q decimal(16,2),
r decimal(16,2),
s decimal(16,2),
t decimal(16,2),
u decimal(16,2),
v integer,
w integer,
x integer,
y integer,
z integer,
aa integer,
bb integer,
cc decimal(8,6),
dd decimal(8,6),
ee decimal(18,2),
ff decimal(18,2),
gg char(8),
hh char(8),
ii char(8),
jj char(8),
kk char(8),
ll char(8),
mm char(8),
nn char(20),
oo char(8),
pp char(11),
qq char(64),
rr char(5),
ss char(6)
) extent size 3200 next size 3200 lock mode row;
create external table aaa_ext
(
a char(11),
b char(11),
c integer,
d integer,
e char(2),
f char(1),
g char(10),
h char(20),
i char(10),
j char(10),
k char(1),
l char(32),
m char(10),
n decimal(16,2),
o decimal(16,2),
p decimal(16,2),
q decimal(16,2),
r decimal(16,2),
s decimal(16,2),
t decimal(16,2),
u decimal(16,2),
v integer,
w integer,
x integer,
y integer,
z integer,
aa integer,
bb integer,
cc decimal(8,6),
dd decimal(8,6),
ee decimal(18,2),
ff decimal(18,2),
gg char(8),
hh char(8),
ii char(8),
jj char(8),
kk char(8),
ll char(8),
mm char(8),
nn char(20),
oo char(8),
pp char(11),
qq char(64),
rr char(5),
ss char(6)
)using(
datafiles("DISK:/backup/demo.exp/aaa106838.unl"),
format "delimited",
DELIMITER "|",
rejectfile "/backup/err",
maxerrors 100
);
insert into aaa select * from aaa_ext;
....
1226: Decimal or money value exceeds maximum precision.
/backup/demo.exp/aaa106838.unl~48405~CONVERT_ERR~cc~0010100|0010101|2057427|2131689|01|2|21101|xxxx||31|000
|0|00230001000000000000000000030005|1100000000|1.05|1.05|33519.57|0.0|0.0|0.0|0.0|0.0|48|10269|269|1|0|0|1000|0.0|0.0
|0.0|0.0|20020305|20040701||20040828|20111221|20110621||0000000111216|20110614|0010101|||570015|~
/backup/demo.exp/aaa106838.unl~55341~CONVERT_ERR~cc~0010100|0010101|2065886|3162600|01|2|21101|xxxxx||31|000|0
|00230001000000000000000000037300|1100000000|1.35|1.35|0.0|0.0|0.0|0.0|0.0|0.0|47|10003|3|3|0|0|1000|0.0|0.0|0.0|0.0|
20020507|20040701||20040828|20111221|20050701||0000000487663|20040701|0010101|||590315|~
/backup/demo.exp/aaa106838.unl~110660~TOO_MANY_FLDS~~0010500|0010501|10118507|6515328|01|2|21101|xxxx||31|0
00|0|00230000000000000000000000007300|1100000000|1.41|1.41|1.24|0.0|0.0|0.0|0.0|0.0|19|91|19|1|0|0|0|0.0|0.0|0.0|0.0|
20070824|20070824||20070824|20111221|20091221||0000001813670|20090930|0010106|||600411|~
但是单独使用load 就能正常完成
load from "/backup/demo.exp/aaa106838.unl" delimiter '|' insert into aaa;