[20160910]sqlldr使用问题.txt
http://www.itpub.net/thread-2067126-1-1.html
CREATE TABLE "SCOTT"."ASSIGNBLOCKS"
( "AB_WOID" VARCHAR2(20),
"AB_TYPE" VARCHAR2(20),
"AB_START" VARCHAR2(20),
"AB_END" VARCHAR2(20),
"AB_DATETIME" DATE
) ;
>cat 1.csv
HB6X IMEID1 35465106025769 35465106036070 2014-11-20 13:46:26.837000000
HB6X IMEID2 35465106525769 35465106536070 2014-11-20 13:46:26.860000000
HB6Y IMEID1 35710406030815 35710406041116 2014-11-20 13:46:27.120000000
HB6Y IMEID2 35710406530815 35710406541116 2014-11-20 13:46:27.133000000
--由于后面字段的日期格式与oracle不同.控制文件定义要加入terminated by "."就ok了.
>cat test.ctl
cat test.ctl
load data
CHARACTERSET ZHS16GBK
infile '1.csv'
into table ASSIGNBLOCKS
fields terminated by x'09'
TRAILING NULLCOLS
(AB_WOID,
AB_TYPE,
AB_START,
AB_END,
AB_DATETIME DATE "YYYY-MM-DD HH24:MI:SS" terminated by ".")
sqlldr userid=scott/btbtms@test01p control=test.ctl log=1.log bad=1.bad discard=1.discard
SCOTT@test01p> select * from ASSIGNBLOCKS;
AB_WOID AB_TYPE AB_START AB_END AB_DATETIME
-------------------- -------------------- -------------------- -------------------- -------------------
HB6X IMEID1 35465106025769 35465106036070 2014-11-20 13:46:26
HB6X IMEID2 35465106525769 35465106536070 2014-11-20 13:46:26
HB6Y IMEID1 35710406030815 35710406041116 2014-11-20 13:46:27
HB6Y IMEID2 35710406530815 35710406541116 2014-11-20 13:46:27
http://www.itpub.net/thread-2067126-1-1.html
CREATE TABLE "SCOTT"."ASSIGNBLOCKS"
( "AB_WOID" VARCHAR2(20),
"AB_TYPE" VARCHAR2(20),
"AB_START" VARCHAR2(20),
"AB_END" VARCHAR2(20),
"AB_DATETIME" DATE
) ;
>cat 1.csv
HB6X IMEID1 35465106025769 35465106036070 2014-11-20 13:46:26.837000000
HB6X IMEID2 35465106525769 35465106536070 2014-11-20 13:46:26.860000000
HB6Y IMEID1 35710406030815 35710406041116 2014-11-20 13:46:27.120000000
HB6Y IMEID2 35710406530815 35710406541116 2014-11-20 13:46:27.133000000
--由于后面字段的日期格式与oracle不同.控制文件定义要加入terminated by "."就ok了.
>cat test.ctl
cat test.ctl
load data
CHARACTERSET ZHS16GBK
infile '1.csv'
into table ASSIGNBLOCKS
fields terminated by x'09'
TRAILING NULLCOLS
(AB_WOID,
AB_TYPE,
AB_START,
AB_END,
AB_DATETIME DATE "YYYY-MM-DD HH24:MI:SS" terminated by ".")
sqlldr userid=scott/btbtms@test01p control=test.ctl log=1.log bad=1.bad discard=1.discard
SCOTT@test01p> select * from ASSIGNBLOCKS;
AB_WOID AB_TYPE AB_START AB_END AB_DATETIME
-------------------- -------------------- -------------------- -------------------- -------------------
HB6X IMEID1 35465106025769 35465106036070 2014-11-20 13:46:26
HB6X IMEID2 35465106525769 35465106536070 2014-11-20 13:46:26
HB6Y IMEID1 35710406030815 35710406041116 2014-11-20 13:46:27
HB6Y IMEID2 35710406530815 35710406541116 2014-11-20 13:46:27