导出生成落地文件的脚本:
SELECT policyno 保单号, tsid 投诉id, aplname 投诉人姓名,
aplmobile 投诉人手机,
TO_CHAR (apldate, 'yyyy-mm-dd hh24:mi:ss') 投诉时间,
apltype 投诉类型,
ASCIISTR (replace(replace(REPLACE (aplmatt, CHR (10), 'A20131225B'),chr(13),'C20131225D'),',','E20131225F')) 投诉内容,
ASCIISTR (replace(replace(REPLACE (dealmemo, CHR (10), 'A20131225B'),chr(13),'C20131225D'),',','E20131225F')) 投诉答复内容,
TO_CHAR (dafdate, 'yyyy-mm-dd hh24:mi:ss') 投诉处理结束时间,
DECODE (validity, 'yes', '有效', 'no', '无效', '未知') 是否有效投诉,
TO_CHAR (apldate, 'yyyy-mm-dd hh24:mi:ss') 计算机输入日期
FROM app_biz_ts
WHERE apldate >= TO_DATE ('2012-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND apldate <= TO_DATE ('2012-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
ORDER BY apldate ASC
SQLloader 入库脚本:
OPTIONS ( ERRORS=100000, ROWS=5000, BINDSIZE=600000 )
LOAD DATA
CHARACTERSET ZHS16GBK
INTO TABLE ods_complain
APPEND
fields terminated by ','
TRAILING NULLCOLS
(
policyno,
comp_id,
comp_name,
comp_tel,
comp_date,
comp_type,
comp_content char(3000),
comp_answer char(3000),
comp_managedate,
comp_status,
input_date
)
入库后更新脚本:
select t.policyno,
t.comp_id,
t.comp_name,
t.comp_tel,
t.comp_date,
t.comp_type,
replace(replace(replace(unistr(comp_content), 'E20131225F', ','),
'A20131225B',
chr(10)),
'C20131225D',
chr(13)),
replace(replace(replace(unistr(comp_answer), 'E20131225F', ','),
'A20131225B',
chr(10)),
'C20131225D',
chr(13)),
t.comp_managedate,
t.comp_status,
t.input_date
from ods_complain t
where t.comp_id = 'M1231000000000048'