随笔-314  评论-209  文章-0  trackbacks-0

#!/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 阅读(548) 评论(0)  编辑  收藏 所属分类: linux/unix

只有注册用户登录后才能发表评论。


网站导航: