在服务器上有一些txe(文本文件),里面对是这样一行一行的记录,差不多有6千条(下面对应着一行记录(14列))
20101020061010|808080123100001|1488015462605121|0001|000000001000|1001|20101020|4007|156|20101020|745147|20101020060855|13776413305|A73E0784555C454927D809A3776952114EFCACA0276C6F8B79FDF55558D316021867784F5E08A8143D6B91ACDFD23AC3975A52C52EFB5CA5FD4744227AA4999513324B043D4521F134A787EBD8C67E5023EC63A7CD2C1920A4E04733C4470F2DE757EE9DEE7F34BAA414901BFBB4419E79787CFFD3254B00A9E6985471C0D3ED现在数据库中有一张表和这个记录相对应。。有如下列,
(accountchid 主键,自动增长
Chinapaydate
MerId
Ordid
TransType
Transamt
Transstat
Transdate
GateId
Curid
cpDate
Cpseqid
longname1
telno
checkValue
)现在我用load把这些文件文件导入到表中,OPTIONS (skip=1)
load data
INFILE 'G:\workspase2\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
into table account_of_check
(
accountchid SEQ_account_of_check.nextVal, -- 主键 我想默认增长,下面的列对应着文件里面的列
Chinapaydate char terminated by '|',
MerId char terminated by '|',
Ordid char terminated by '|',
TransType char terminated by '|',
Transamt char terminated by '|',
Transstat char terminated by '|',
Transdate char terminated by '|',
GateId char terminated by '|',
Curid char terminated by '|',
cpDate char terminated by '|',
Cpseqid char terminated by '|',
longname1 char terminated by '|',
telno char terminated by '|',
checkValue char terminated by '|',
)
sqlldr xwmall/[email protected]/oradb control =E:\Noname1.ctl可这样插入不了如果把“char terminated by '|' ”分割符去掉了,就只把第一列插入进去了。。请问这个怎么解决
20101020061010|808080123100001|1488015462605121|0001|000000001000|1001|20101020|4007|156|20101020|745147|20101020060855|13776413305|A73E0784555C454927D809A3776952114EFCACA0276C6F8B79FDF55558D316021867784F5E08A8143D6B91ACDFD23AC3975A52C52EFB5CA5FD4744227AA4999513324B043D4521F134A787EBD8C67E5023EC63A7CD2C1920A4E04733C4470F2DE757EE9DEE7F34BAA414901BFBB4419E79787CFFD3254B00A9E6985471C0D3ED现在数据库中有一张表和这个记录相对应。。有如下列,
(accountchid 主键,自动增长
Chinapaydate
MerId
Ordid
TransType
Transamt
Transstat
Transdate
GateId
Curid
cpDate
Cpseqid
longname1
telno
checkValue
)现在我用load把这些文件文件导入到表中,OPTIONS (skip=1)
load data
INFILE 'G:\workspase2\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
into table account_of_check
(
accountchid SEQ_account_of_check.nextVal, -- 主键 我想默认增长,下面的列对应着文件里面的列
Chinapaydate char terminated by '|',
MerId char terminated by '|',
Ordid char terminated by '|',
TransType char terminated by '|',
Transamt char terminated by '|',
Transstat char terminated by '|',
Transdate char terminated by '|',
GateId char terminated by '|',
Curid char terminated by '|',
cpDate char terminated by '|',
Cpseqid char terminated by '|',
longname1 char terminated by '|',
telno char terminated by '|',
checkValue char terminated by '|',
)
sqlldr xwmall/[email protected]/oradb control =E:\Noname1.ctl可这样插入不了如果把“char terminated by '|' ”分割符去掉了,就只把第一列插入进去了。。请问这个怎么解决
INFILE 'G:\workspase2\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
into table account_of_check
fields terminated by '|'
(
Chinapaydate,
.....
)
accountchid可以在数据装载完毕后,使用udpate更新。
udpate account_of_check t set t. account_of_check=SEQ_account_of_check.nextVal;
OPTIONS (skip=1)
load data
INFILE 'G:\workspase2\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
fields terminated by '|'
into table account_of_check
(
Chinapaydate char terminated by '|',
MerId char terminated by '|',
Ordid char terminated by '|',
TransType char terminated by '|',
Transamt char terminated by '|',
Transstat char terminated by '|',
Transdate char terminated by '|',
GateId char terminated by '|',
Curid char terminated by '|',
cpDate char terminated by '|',
Cpseqid char terminated by '|',
longname1 char terminated by '|',
telno char terminated by '|',
checkValue char terminated by whitespace
)
/
udpate account_of_check t set t. account_of_check=SEQ_account_of_check.nextVal;
是这样吗,也和load写到一个 .ctl文件里面么?????
只能在外部执行,不能放在ctl里面。
OPTIONS (skip=1)
load data
INFILE 'G:\workspase2\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
into table account_of_check
fields terminated by '|'
(
Chinapaydate ,
MerId ,
Ordid ,
TransType ,
Transamt ,
Transstat ,
Transdate ,
GateId ,
Curid ,
cpDate ,
Cpseqid ,
longname1 ,
telno ,
checkValue
)
这句放在into table account_of_check
后面试试
OPTIONS (skip=1)
load data
INFILE 'G:\workspase\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
into table account_of_check
FIELDS TERMINATED BY ","
(
Chinapaydate,
MerId,
Ordid,
TransType,
Transamt,
Transstat,
Transdate,
GateId,
Curid,
cpDate,
Cpseqid,
longname1,
telno,
checkValue
)我这样写的啊。
去掉的话,就会把第一列插入里面
这行没用吧,去掉
808080123100001_20101020_20101021042834.txt文件是用','分割的吗?
应该是'|'
用下边的试试load data
INFILE 'G:\workspase\Test\src\resource\test\808080123100001_20101020_20101021042834.txt'
append
into table account_of_check
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
Chinapaydate,
MerId,
Ordid,
TransType,
Transamt,
Transstat,
Transdate,
GateId,
Curid,
cpDate,
Cpseqid,
longname1,
telno,
checkValue
)
OPTIONS (skip=1)
load data
INFILE 'G:\workspase2\Test\src\resource\test\808080123100001_20101020_201010210428342.txt'
append into table account_of_check
FIELDS TERMINATED BY '|'
(
Chinapaydate,
MerId,
Ordid,
TransType,
Transamt,
Transstat,
Transdate,
GateId,
Curid,
cpDate,
Cpseqid,
longname1,
telno,
checkValue,
FILLER_1 FILLER,
accountchid "SEQ_account_of_check.nextVal"
)20101020061010|808080123100001|1488015462605121|0001|000000001000|1001|20101020|4007|156|20101020|745147|20101020060855|13776413305|A73E0784555C454927D809A3776952114EFCACA0276C6F8B79FDF55558D316021867784F5E08A8143D6B91ACDFD23AC3975A52C52EFB5CA5FD4744227AA4999513324B043D4521F134A787EBD8C67E5023EC63A7CD2C1920A4E04733C4470F2DE757EE9DEE7F34BAA414901BFBB4419E79787CFFD3254B00A9E6985471C0D3ED
这是文件中的一条记录,兄弟你的联系方式是什么
怎么生成这个日志文件啊sqlldr xwmall/[email protected]/oradb control =E:\Noname1.ctl logfile=Noname1.log;这样语法 不对啊。。
SQL*Loader: Release 10.2.0.1.0 - Production on 星期五 7月 15 16:46:27 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.控制文件: E:\Noname1.ctl
数据文件: E:\808080123100001_20101020_201010210428342.txt
错误文件: E:\808080123100001_20101020_201010210428342.bad
废弃文件: 未作指定
(可废弃所有记录)要加载的数: ALL
要跳过的数: 1
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续: 未作指定
所用路径: 常规表 ACCOUNT_OF_CHECK2,已加载从每个逻辑记录
插入选项对此表 APPEND 生效 列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
CHINAPAYDATE FIRST * | CHARACTER
MERID NEXT * | CHARACTER
ORDID NEXT * | CHARACTER
TRANSTYPE NEXT * | CHARACTER
TRANSAMT NEXT * | CHARACTER
TRANSSTAT NEXT * | CHARACTER
TRANSDATE NEXT * | CHARACTER
GATEID NEXT * | CHARACTER
CURID NEXT * | CHARACTER
CPDATE NEXT * | CHARACTER
CPSEQID NEXT * | CHARACTER
LONGNAME1 NEXT * | CHARACTER
TELNO NEXT * | CHARACTER
CHECKVALUE NEXT * | CHARACTER 记录 1: 被拒绝 - 表 ACCOUNT_OF_CHECK2 的列 CHECKVALUE 出现错误。
数据文件的字段超出最大长度
记录 2: 被拒绝 - 表 ACCOUNT_OF_CHECK2 的列 CHECKVALUE 出现错误。
数据文件的字段超出最大长度表 ACCOUNT_OF_CHECK2:
0 行 加载成功。
由于数据错误, 2 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
为绑定数组分配的空间: 231168 字节 (64 行)
读取 缓冲区字节数: 1048576跳过的逻辑记录总数: 1
读取的逻辑记录总数: 2
拒绝的逻辑记录总数: 2
废弃的逻辑记录总数: 0从 星期五 7月 15 16:46:27 2011 开始运行
在 星期五 7月 15 16:46:27 2011 处运行结束经过时间为: 00: 00: 00.09
CPU 时间为: 00: 00: 00.03
我这个CHECKVALUE 这一列开始定义成varchar2(256),报这个错,后来改成 varchar2(5000),还是这个错