最近写了一个ctl 用于导入数据 格式如下:options (rows=40000,bindsize=209714452,readsize=209714452,skip_index_maintenance=true)
load data
infile '/opt/oracle/cbiz128.txt'
append into table T_BIZ
TRAILING NULLCOLS
(BIZID integer TERMINATED BY ',',
CUSTID integer TERMINATED BY ',',
TCOUNT integer TERMINATED BY ',',
SENDDATE date 'yyyy-mm-dd hh24:mi:ss' TERMINATED BY ',',
SCHDATE date 'yyyy-mm-dd hh24:mi:ss' TERMINATED BY x '0A')但因为最后一个字段是空的 导入一直导不进去 ,数据如下:
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,最后一个字段是日期型的 ,求解
load data
infile '/opt/oracle/cbiz128.txt'
append into table T_BIZ
TRAILING NULLCOLS
(BIZID integer TERMINATED BY ',',
CUSTID integer TERMINATED BY ',',
TCOUNT integer TERMINATED BY ',',
SENDDATE date 'yyyy-mm-dd hh24:mi:ss' TERMINATED BY ',',
SCHDATE date 'yyyy-mm-dd hh24:mi:ss' TERMINATED BY x '0A')但因为最后一个字段是空的 导入一直导不进去 ,数据如下:
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,
20120120115648,88364,1000,2012-01-20 12:01:21,最后一个字段是日期型的 ,求解
解决方案 »
- SQLSERVER2008R Nchar数据插入ORACLE字符长度问题
- 求oracle job定时器那里错误!
- SQL Handler for Oracle in Windows/Linux/Unix
- Oracle 10g express能否生成带用insert语句插入全部数据的SQL脚本? (generate DDL)
- SELECT COUNT(*) FROM User_Tab_Columns WHERE upper(Table_Name) = upper('表名')的问题
- 有关UNION查询结果的唯一性问题
- oracle8i数据导出问题,对含有blob列的表一直导不出来?
- 寻求把中文字符串转换为十六进制的串的ORACLE函数?
- 请教
- 网上哪里有免费的技术文档?
- 怎样防止表锁定哦?
- oracle写了个简单的分页,一直报右括号缺失
load data
infile '/opt/oracle/cbiz128.txt'
append into table T_BIZ
fields terminated by ','
(BIZID integer,CUSTID integer,TCOUNT integer,SENDDATE date 'yyyy-mm-dd hh24:mi:ss',SCHDATE date 'yyyy-mm-dd hh24:mi:ss')
就会报错显示为 Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BIZID FIRST 4 INTEGER
CUSTID NEXT 4 INTEGER
TCOUNT NEXT 4 INTEGER
SENDDATE NEXT * , DATE yyyy-mm-dd hh24:mi:ss
SCHDATE NEXT * , DATE yyyy-mm-dd hh24:mi:ssRecord 1: Rejected - Error on table T_BIZ, column SENDDATE.
ORA-01840: input value not long enough for date format
load data
infile '/opt/oracle/cbiz128.txt'
append into table T_BIZ
fields terminated by ','
TRAILING NULLCOLS ---你试试,主要是少了这个参数
(BIZID integer,CUSTID integer,TCOUNT integer,SENDDATE date 'yyyy-mm-dd hh24:mi:ss',SCHDATE date 'yyyy-mm-dd hh24:mi:ss')
贴出我的ctl文件(windows下的)
load data
infile 'd:\33.txt'
append into table T_BIZ
fields terminated by ','
TRAILING NULLCOLS
(BIZID,
CUSTID,
TCOUNT,
SENDDATE DATE 'YYYY-MM-DD HH24:MI:SS',
SCHDATE DATE 'YYYY-MM-DD HH24:MI:SS'
)