sqlload杂记

使用方法
1、将ctl和csv文件放入c盘
2、进入cmd下，进入C盘盘符
3、输入以下命令
C:>sqlldr userid=dbsaleopr/ABcd.12#43@tmanadb control=load.ctl
注意事项：
csv文件的最后一列，不能是空，必须有个值。

示例1：

sqlldr userid=dbmktrptadm/ABcd.12#43@kf_rpt control=mk_phoneaccountout_info.ctl direct=y

load data
CHARACTERSET AL32UTF8
infile ‘D:/ldr/mk_phoneaccountout_info.txt’
into table dbmktrptadm.mk_phoneaccountout_info
Append
TRAILING NULLCOLS
(
phone_no char terminated by “|” ,
total_fee char terminated by “|” ,
prod_fee char terminated by “|” ,
total_month “to_char(add_months(sysdate,-1),‘yyyymm’)”,
oper_time “sysdate” ,
flow_fee char terminated by whitespace)

示例2

ZHS16GBK解决中文乱码
–filler过滤字段，即文档中的根据|分割的字段过滤不处理。
sqlldr userid=dbsaleopr/ABcd.12#43@tmanadb control=load.ctl

load data
CHARACTERSET ZHS16GBK
infile ‘c:/20161102.csv’ badfile ‘c:/20161102.bat’ discardfile ‘c:/20161102.discard’
Append into table dbsaleadm.se_custresource_info
fields terminated by ‘|’ OPTIONALLY enclosed by X’0C’
TRAILING NULLCOLS
(
filler_10 filler,
UPDATE_DATE sysdate,
filler_11 filler,
filler_13 filler,
filler_14 filler,
CRM_CUST_ID “trim(:CRM_CUST_ID)”,
POP “trim(:POP)”,
TRANSFER “trim(:TRANSFER)”,
OPTICAL “trim(:OPTICAL)”,
CUST_ID “trim(:CUST_ID)”
)