-----控制文件如下:
[oracle@CMSINTE1 ftp_user]$ cat crm_gw_day.ctl
load data
infile PROD_GW_#599_D_130415_A_NM_F1.ITF
badfile '/oradata/ftp_user/gw_crm_day.bad'
DISCARDMAX 10
append
into table EDW_CRM_GW_DAY_SRC
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
(LATN_ID ,
BUREAU_KEY ,
PROD_ID ,
PROP_CUST_ID,
CUST_ID ,
PROD_SPEC_ID,
PROD_SPEC_TYPE ,
CRT_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
MDFY_DT timestamp 'yyyy-mm-dd hh24:mi:ss',
STAFF ,
PROD_ST_CODE,
SERV_ACC_NBR,
ADDR_ID ,
EXCH_ID ,
CUST_NAME ,
CUST_GRADE_ID ,
CITY_FLAG ,
FILE_CYCLE timestamp 'yyyy-mm-dd hh24:mi:ss',
FILE_NBR,
NEW_FLAG_ID ,
TRADE_TYPE_ID ,
JOIN_DURATION_LEVEL2_ID ,
MDSE_SPEC_ID,
ACCOUNT ,
REGION_ID ,
RENT_TYPE ,
ADDRESS_NAME,
MAIN_GROUP_ID ,
BRAND_ID,
IS_BILL_USER,
IS_AVAIL_USER ,
PAY_TYPE,
CHAN_ID ,
NBR_HEAD,
STOP_FLAG ,
REMOVE_FLAG ,
PREFER_ID ,
PREFER_NAME ,
PAG_FLAG,
PREFER_VALUE,
PREFER_PROD_ID ,
PREFER_EEF_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_EXP_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_CRT_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_MDFY_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_STATE,
PRICE_ID,
PRICE_NAME ,
PRICE_ID_JT ,
CUST_TYPE_LEVEL_4_ID ,
CHANEL_TYPE_ID ,
SHLR_ID ,
FREE_FLG,
TC_CLASS,
CUST_CODE ,
COMM_ORG_ID ,
DEV_TE_ID ,
DEAL_TE_ID ,
DEV_STAFF_ID,
DEAL_STAFF_ID ,
SALE_DT timestamp 'yyyy-mm-dd hh24:mi:ss',
PROD_FUNC_TYPE ,
CUST_CUR_YEAR_TYPE ,
CUST_SERVICE_LEVEL ,
CUST_CREDIT_LEVEL ,
CUST_CREDIT_VALUE ,
BIRTHDAY timestamp 'yyyy-mm-dd hh24:mi:ss',
AGE,
SEX,
CONTACT_NAME,
CONTACT_PHONE)-----SH文件如下:
[oracle@CMSINTE1 ftp_user]$ cat crm_gw_day.sh
#!/bin/bash
#Setup Oracle Enviroment
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
ORACLE_BASE=/oracle/app/oracle/product/10.2.0
export ORACLE_BASE
ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_HOME
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG
PATH=$PATH:$ORACLE_HOME/bin
export PATHfilepath='/oradata/ftp_user/';
filelog='crm_gw_day_imp.log';
filename_src='crm_gw_day_src.ctl';
filename='crm_gw_day.ctl';date>> $filepath/$filelog;echo "begin">>$filepath/$filelog;cd $filepath;for var in `find $filepath -name "PROD_GW_#*_D_*_A_NM_F1.ITF" -exec basename {} \;` ; do echo ${var}>> $filepath/$filelog;if [ -f "test.ctl" ]; then
rm $filepath/$filename;
ficp $filepath/$filename_src $filepath/$filename;
sed -i 's/infile/infile '${var}'/g' $filepath/$filename;
sqlldr userid=dw/cms_dw@fj_cms_145 control=$filepath/$filename direct=y;cat crm_gw_day.log>>crm_gw_day_log.log;mv $filepath/${var} /oradata/bakdata;
doneecho "end">>$filepath/$filelog ;
[oracle@CMSINTE1 ftp_user]$ ----------------------------------------------------
求好心人士,详细讲解下以上shell脚本的具体执行业务逻辑,谢谢
[oracle@CMSINTE1 ftp_user]$ cat crm_gw_day.ctl
load data
infile PROD_GW_#599_D_130415_A_NM_F1.ITF
badfile '/oradata/ftp_user/gw_crm_day.bad'
DISCARDMAX 10
append
into table EDW_CRM_GW_DAY_SRC
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
(LATN_ID ,
BUREAU_KEY ,
PROD_ID ,
PROP_CUST_ID,
CUST_ID ,
PROD_SPEC_ID,
PROD_SPEC_TYPE ,
CRT_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
MDFY_DT timestamp 'yyyy-mm-dd hh24:mi:ss',
STAFF ,
PROD_ST_CODE,
SERV_ACC_NBR,
ADDR_ID ,
EXCH_ID ,
CUST_NAME ,
CUST_GRADE_ID ,
CITY_FLAG ,
FILE_CYCLE timestamp 'yyyy-mm-dd hh24:mi:ss',
FILE_NBR,
NEW_FLAG_ID ,
TRADE_TYPE_ID ,
JOIN_DURATION_LEVEL2_ID ,
MDSE_SPEC_ID,
ACCOUNT ,
REGION_ID ,
RENT_TYPE ,
ADDRESS_NAME,
MAIN_GROUP_ID ,
BRAND_ID,
IS_BILL_USER,
IS_AVAIL_USER ,
PAY_TYPE,
CHAN_ID ,
NBR_HEAD,
STOP_FLAG ,
REMOVE_FLAG ,
PREFER_ID ,
PREFER_NAME ,
PAG_FLAG,
PREFER_VALUE,
PREFER_PROD_ID ,
PREFER_EEF_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_EXP_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_CRT_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_MDFY_DT timestamp 'yyyy-mm-dd hh24:mi:ss' ,
PREFER_STATE,
PRICE_ID,
PRICE_NAME ,
PRICE_ID_JT ,
CUST_TYPE_LEVEL_4_ID ,
CHANEL_TYPE_ID ,
SHLR_ID ,
FREE_FLG,
TC_CLASS,
CUST_CODE ,
COMM_ORG_ID ,
DEV_TE_ID ,
DEAL_TE_ID ,
DEV_STAFF_ID,
DEAL_STAFF_ID ,
SALE_DT timestamp 'yyyy-mm-dd hh24:mi:ss',
PROD_FUNC_TYPE ,
CUST_CUR_YEAR_TYPE ,
CUST_SERVICE_LEVEL ,
CUST_CREDIT_LEVEL ,
CUST_CREDIT_VALUE ,
BIRTHDAY timestamp 'yyyy-mm-dd hh24:mi:ss',
AGE,
SEX,
CONTACT_NAME,
CONTACT_PHONE)-----SH文件如下:
[oracle@CMSINTE1 ftp_user]$ cat crm_gw_day.sh
#!/bin/bash
#Setup Oracle Enviroment
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
ORACLE_BASE=/oracle/app/oracle/product/10.2.0
export ORACLE_BASE
ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_HOME
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG
PATH=$PATH:$ORACLE_HOME/bin
export PATHfilepath='/oradata/ftp_user/';
filelog='crm_gw_day_imp.log';
filename_src='crm_gw_day_src.ctl';
filename='crm_gw_day.ctl';date>> $filepath/$filelog;echo "begin">>$filepath/$filelog;cd $filepath;for var in `find $filepath -name "PROD_GW_#*_D_*_A_NM_F1.ITF" -exec basename {} \;` ; do echo ${var}>> $filepath/$filelog;if [ -f "test.ctl" ]; then
rm $filepath/$filename;
ficp $filepath/$filename_src $filepath/$filename;
sed -i 's/infile/infile '${var}'/g' $filepath/$filename;
sqlldr userid=dw/cms_dw@fj_cms_145 control=$filepath/$filename direct=y;cat crm_gw_day.log>>crm_gw_day_log.log;mv $filepath/${var} /oradata/bakdata;
doneecho "end">>$filepath/$filelog ;
[oracle@CMSINTE1 ftp_user]$ ----------------------------------------------------
求好心人士,详细讲解下以上shell脚本的具体执行业务逻辑,谢谢
解决方案 »
- 我想知道这算不算是oracle中的bug
- oracle 数据库中自定义全局变量
- 菜鸟我在学习oracle, 学到序列时候,概念是看明白了,但是不明白各们大虾是什么时候用到的,能不能举例?谢谢!
- 在线等 求一个语句转换 高分
- 请教一个SQL。。。
- 如果增加 Oracle9i 的编码,100分!
- xcopy的时候,要copy带有.dll的文件,但是这些文件中,有一个文件,不能copy,该怎么过滤写这个?
- 请哪位大侠帮我修改一下一个plsql过程
- 在oracle中 execute immediate 能返回成功失败等信息吗
- 使用SQL*Loader导入数据时,总是只能到入第一个文件
- oracle11 连接数据库偶尔超时,紧急
- 急急急两表数据更新问题
sed -i 's/infile/infile '${var}'/g' $filepath/$filename;
将ctl 文件的infile 行 换成 infile + 数据文件
sqlldr userid=dw/cms_dw@fj_cms_145 control=$filepath/$filename direct=y;
将数据导入表中总体上就是把所有PROD_GW_#*_D_*_A_NM_F1.ITF 文件里的数据导入表EDW_CRM_GW_DAY_SRC