[20160910]sqlldr使用問題.txt

[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