CTL文件格式及生成字段

1、CTL文件内容
OPTIONS(BINDSIZE=2097152,READSIZE=2097152,ROWS=50000)
LOAD DATA
CHARACTERSET ZHS16GBK
LENGTH CHARACTER
INFILE ‘/logs/data/$date/EDW/文件名.f.$date.dat’
TRUNCATE INTO TABLE ODS.表名
FIELDS TERMINATED BY X’1b’
TRAILING NULLCOLS
(
“ETL_DT” DATE “YYYY-MM-DD HH24:MI:SS” NULLIF “ETL_DT” =BLANKS,
“LP_ID” CHAR(60) NULLIF “LP_ID” =BLANKS,
“CUST_ACCT_ID” CHAR(60) NULLIF “CUST_ACCT_ID” =BLANKS,
“CUST_ACCT_NAME” CHAR(300) NULLIF “CUST_ACCT_NAME” =BLANKS,
“CUST_ID” CHAR(60) NULLIF “CUST_ID” =BLANKS,
“MAX_SUB_ACCT_NUM” CHAR(60) NULLIF “MAX_SUB_ACCT_NUM” =BLANKS,
“STD_PROD_ID” CHAR(60) NULLIF “STD_PROD_ID” =BLANKS,
“DRAWDOWN_WAY_CD” CHAR(10) NULLIF “DRAWDOWN_WAY_CD” =BLANKS,
“ACCT_STATUS_CD” CHAR(10) NULLIF “ACCT_STATUS_CD” =BLANKS,
“ACCT_DRAWDOWN_WAY_STATUS” CHAR(10) NULLIF “ACCT_DRAWDOWN_WAY_STATUS” =BLANKS,
“FROZ_STATUS_CD” CHAR(10) NULLIF “FROZ_STATUS_CD” =BLANKS,
“STOP_PAY_STATUS_CD” CHAR(10) NULLIF “STOP_PAY_STATUS_CD” =BLANKS,
“ACPT_PAY_STATUS_CD” CHAR(10) NULLIF “ACPT_PAY_STATUS_CD” =BLANKS,
“ACCT_USAGE_CD” CHAR(10) NULLIF “ACCT_USAGE_CD” =BLANKS,
“SLEEP_ACCT_FLG” CHAR(10) NULLIF “SLEEP_ACCT_FLG” =BLANKS,
“DORMT_ACCT_FLG” CHAR(10) NULLIF “DORMT_ACCT_FLG” =BLANKS,
“PRIVAVY_ACCT_FLG” CHAR(10) NULLIF “PRIVAVY_ACCT_FLG” =BLANKS,
“ACCT_BELONG_ORG_ID” CHAR(60) NULLIF “ACCT_BELONG_ORG_ID” =BLANKS,
“OPEN_ACCT_ORG_ID” CHAR(60) NULLIF “OPEN_ACCT_ORG_ID” =BLANKS,
“OPEN_ACCT_TELLER_ID” CHAR(60) NULLIF “OPEN_ACCT_TELLER_ID” =BLANKS,
“OPEN_ACCT_CHN_CD” CHAR(10) NULLIF “OPEN_ACCT_CHN_CD” =BLANKS,
“OPEN_ACCT_FLOW_NUM” CHAR(60) NULLIF “OPEN_ACCT_FLOW_NUM” =BLANKS,
“OPEN_ACCT_DT” DATE “YYYY-MM-DD HH24:MI:SS” NULLIF “OPEN_ACCT_DT” =BLANKS,
“OPEN_ACCT_TM” TIMESTAMP “YYYY-MM-DD HH24:MI:SSXFF” NULLIF “OPEN_ACCT_TM” =BLANKS,
“CLOSE_ACCT_ORG_ID” CHAR(60) NULLIF “CLOSE_ACCT_ORG_ID” =BLANKS,
“CLOS_ACCT_TELLER_ID” CHAR(60) NULLIF “CLOS_ACCT_TELLER_ID” =BLANKS,
“CLOS_ACCT_FLOW_NUM” CHAR(60) NULLIF “CLOS_ACCT_FLOW_NUM” =BLANKS,
“CLOS_ACCT_DT” DATE “YYYY-MM-DD HH24:MI:SS” NULLIF “CLOS_ACCT_DT” =BLANKS,
“CLOS_ACCT_TM” TIMESTAMP “YYYY-MM-DD HH24:MI:SSXFF” NULLIF “CLOS_ACCT_TM” =BLANKS
)

2、生成CTL字段：
select rpad(’"’||column_name||’"’,30,’ ‘) as key,
case when data_type=‘VARCHAR2’ then ‘CHAR(’||data_length||’)’
when data_type=‘DATE’ then ‘DATE “YYYY-MM-DD HH24:MI:SS”’
when data_type=‘NUMBER’ then ‘CHAR(’||(data_precision+data_scale)||’)’
when data_type=‘TIMESTAMP(6)’ then ‘TIMESTAMP “YYYY-MM-DD HH24:MI:SSXFF”’
end as content,
rpad(‘NULLIF "’||column_name||’"’,40,’ ‘) as content2,’ =BLANKS,’ as other
from user_tab_columns
where table_name=‘表名’
order by column_id

3、shell调用CTL
#######################################

programName：xx.sh

des：load dw data

version：V1.0

author：XX

time：XX

#######################################
#!/bin/bash
source /home/weblogic/app/ssh/config

bapp_db_ip= b a p p d b i