#!/sbin/sh
######################################
## 名称: infuser_load.sh
## 描述: 通用接口文件 导入数据库
## 参数: owner table_name
## 作者: xxx
## 日期: 2011-03-04
######################################
##owner
owner=$1
##table_name
table_name=$2
##batchId
batchId=$3
##day_id
day_id=$4
##日期[YYYYMMDD]
DAYID=`date +'%Y%m%d'`
##月份[YYYYMM]
MONTHID=`date +'%Y%m'`
##shell文件目录
sh_dir=/inffile/shell/
cd ${sh_dir}
##load文件###########################
##file_name
file_name=`sqlplus -s infuser/xxx@DATACK <<EOF
set heading off feedback off pagesize 0 verify off echo off
select replace(replace(to_char(file_name), '@DAYID@', '${DAYID}'), '@MONTHID@', '${MONTHID}')
from datackdb.inf_file_def
where owner = '${owner}'
and table_name = '${table_name}'
and state = '00A'
and rownum <= 1;
exit
EOF`
#echo "${file_name}"
##ctl_file
ctl_file=`sqlplus -s infuser/xxx@DATACK <<EOF
set heading off feedback off pagesize 0 verify off echo off
select replace(replace(to_char(ctl_file), '@DAYID@', '${DAYID}'), '@MONTHID@', '${MONTHID}')
from datackdb.inf_file_def
where owner = '${owner}'
and table_name = '${table_name}'
and state = '00A'
and rownum <= 1;
exit
EOF`
#echo "${ctl_file}"
infile=""
for fname in $file_name
do
if [ -r ${fname} ]
then
infile=$infile"INFILE '${fname}'\n"
fi
done
#是否包含@INFILE@字符串的判断
if echo "$ctl_file"|grep -q "@INFILE@"
then
#分隔符前字符串
echo "${ctl_file%%@INFILE@*}" >${table_name}.ctl
#文件名
echo "${infile}" >>${table_name}.ctl
#分隔符后字符串
echo "${ctl_file##*@INFILE@}" >>${table_name}.ctl
else
echo "${ctl_file}" >${table_name}.ctl
fi
#导入数据
sqlldr infuser/infuser@DATACK control=${table_name}.ctl direct=y errors=1000
#删除控制文件
#rm ${table_name}.ctl
#rm ${table_name}.log
##写消息
sqlplus infuser/infuser@DATACK <<EOF
insert into datackdb.inf_data_msg (OWNER, TABLE_NAME, FWF_NO, LAN_ID, DAY_ID, STATE, STATE_DATE, COMMENTS)
values ('${owner}', '${table_name}', '${batchId}', -1, '${day_id}', '00A', sysdate, '');
exit
EOF
date +'%Y-%m-%d %T' >>param.txt
echo "$0 $*" >>param.txt
echo "$0 $* -- 成功"
posted on 2011-03-04 15:23
xzc 阅读(544)
评论(0) 编辑 收藏 所属分类:
linux/unix