自动ftp,生成control file，sql*load的shell脚本

这是一个自动从各数据源利用ftp获取文本文件，

利用本地各个数据源目录下已有的seed.ctl动态生成sql*loader controlfile，

然后用sql*loader将数据倒入数据库，

并获取summary information，send mail to users。

第一次写了这么长的shell，很有成就感 :D

(因为需要一些环境配置，该shell无法运行，但是里面一些脚本自己觉得还是很满意，尤其是动态生成控制文件部分，更详细的信息直接看脚本吧)

[@more@] #/usr/bin/bash                                                        
 
nad_env()
{
LOG_DATE=`date "+%Y%m%d"`
EMAIL1=w4897c@motorola.com
EMAIL2=****@motorola.com
EMAIL_CONTENT=/tmp/CRL_MAIL.$LOG_DATE
export EMAIL_CONTENT
}

nad_ftp()
{
FILE_TYPE=$1
FTP_LOG_FILE=./log/ftp_${FILE_TYPE}_${LOG_DATE}.log
LPATH=./in/${FILE_TYPE}

if [ ${FILE_TYPE} == 'ORDER' ]; then
    FILE_EXT="moto_oe_cell_SB${LOG_DATE}*.dat"
    HOST=144.***.**.*** 
    USERNAME=orders
    PASSWORD=******
    RPATH=.
else
    FILE_EXT="*.ZIP"
    HOST=....
    USERNAME=
    PASSWORD=
    RPATH=
fi


echo "*****************************************" >> ${FTP_LOG_FILE}
echo "Begin ftp from $HOST " `date` >> ${FTP_LOG_FILE}
echo "*****************************************" >> ${FTP_LOG_FILE}

ftp -i -n>>/dev/null<open $HOST
user $USERNAME $PASSWORD
cd $RPATH
lcd $LPATH
bin
mget ${FILE_EXT}
bye
EOF


cd ./in/${FILE_TYPE}

if [ -e *.ZIP ]; then
  for FILE_NAME in `ls *.ZIP|cut -d "." -f 1`
  do
    unzip ${FILE_NAME}.ZIP
    rm ${FILE_NAME}.ZIP
  done
fi

if [ -e *.dat ]; then
  for FILE_NAME in `ls *.dat|cut -d "." -f 1`
  do
    mv ${FILE_NAME}.dat ${FILE_NAME}.txt
  done
fi 

cd ../..

echo "**********************************" >> ${FTP_LOG_FILE}
echo "FTP FINISHED" `date`>> ${FTP_LOG_FILE}
echo "**********************************" >> ${FTP_LOG_FILE}
}


nad_ldr()
{
FILE_TYPE=$1

for FILE_NAME in `ls in/${FILE_TYPE}/*.txt|cut -d "." -f 1|cut -d "/" -f 3`
do
  echo "FILE_NAME is : "$FILE_NAME
  cp ./in/${FILE_TYPE}/seed.ctl ./in/${FILE_TYPE}/${FILE_NAME}.ctltmp
  sed -e "s/seed/${FILE_NAME}/g" ./in/${FILE_TYPE}/${FILE_NAME}.ctltmp > ./in/${FILE_TYPE}/${FILE_NAME}.ctl
  rm ./in/${FILE_TYPE}/${FILE_NAME}.ctltmp
done

echo "FILE_TYPE is: " ${FILE_TYPE}

case ${FILE_TYPE} in
  DARTMAIL) sqlplus -s siebel/siebel>>/dev/null<truncate table  NAD_LIST_CON_TMP;
EOF
   SKIP_NUM=1
;;
  XPEDITE) sqlplus -s siebel/siebel>>/dev/null<truncate table NAD_LIST_CON_TMP;
EOF
   SKIP_NUM=0
;;
  EMAILRESP) sqlplus -s siebel/siebel>>/dev/null<truncate table NAD_RESPONSE_TMP;
EOF
   SKIP_NUM=0
;;
  MODEL) sqlplus -s siebel/siebel>>/dev/null<   truncate table NAD_DC_MODEL_TMP;
EOF
   SKIP_NUM=0
  ;;
  ORDER) sqlplus -s siebel/siebel>>/dev/null<   truncate table NAD_ORDER_INTERIM;
EOF
   SKIP_NUM=0
   ;;
  PRODREG) sqlplus -s siebel/siebel>>/dev/null<      truncate table NAD_PRODREG_TMP;
EOF
   SKIP_NUM=0
      ;;
  PROMOREG) sqlplus -s siebel/siebel>>/dev/null<truncate table NAD_PROMOREG_TMP;
EOF
   SKIP_NUM=0
;;
  REBATE) sqlplus -s siebel/siebel>>/dev/null<truncate table NAD_REBATE_TMP;
EOF
   SKIP_NUM=0
;;
  UNSUB) sqlplus -s siebel/siebel>>/dev/null<truncate table NAD_UNSUB_TMP;
EOF
   SKIP_NUM=0
;;
esac


for FILE_NAME in `ls in/${FILE_TYPE}/*.txt|cut -d "." -f 1|cut -d "/" -f 3`
{
  sqlldr siebel/siebel control=./in/${FILE_TYPE}/${FILE_NAME}.ctl log=./log/${FILE_NAME}.log bad=./bad/${FILE_NAME}.bad errors=9000000 skip=${SKIP_NUM}

  FLAT_FILE_NAME="./in/${FILE_TYPE}/${FILE_NAME}.txt"
  LOG_FILE_NAME="./log/${FILE_NAME}.log"
  if [ -e "./bad/${FILE_NAME}.bad" ]; then
    BAD_FILE_NAME="./bad/${FILE_NAME}.bad"
    BAD_RECDS=`cat ${BAD_FILE_NAME}|wc -l`
  else
    BAD_FILE_NAME="null"
    BAD_RECDS=0
  fi
  START_DATE=`grep "Run began on" ${LOG_FILE_NAME}|awk '{print $5,$6,$7,$8}'`
  END_DATE=`grep "Run ended on" ${LOG_FILE_NAME}|awk '{print $5,$6,$7,$8}'`
  TOTAL_RECDS=`cat ${FLAT_FILE_NAME}|wc -l`
  LOAD_RECDS=`cat ${LOG_FILE_NAME}|grep "Rows successfully loaded"|awk '{print $1}'`
  if [ ${BAD_RECDS} -gt 5 ]; then
    ACTION_OUTCOME="Loading failed"
  else
    ACTION_OUTCOME="Loading succeed"
  fi

  sqlplus -s siebel/siebel>/dev/null<    SET FEED OFF
    SET HEAD OFF
    SET TERM OFF
    insert into nad_eventlog
    (
    ACTION_NAME,
    ACTION_START_DATE,
    ACTION_FINISH_DATE,
    ACTION_OUTCOME,
    FLAT_FILE_NAME,
    LOG_FILE_NAME,
    BAD_FILE_NAME,
    NBR_OF_TOTAL_RECORDS,
    NUMBER_OF_RECORDS_LOADED,
    NBR_OF_REJECTED_RECORDS
)
    values(
    'Data Loading--'||'${FILE_TYPE}',
    to_date('${START_DATE}','Mon dd hh24:mi:ss yyyy'),
    to_date('${END_DATE}','Mon dd hh24:mi:ss yyyy'),
    '${ACTION_OUTCOME}',
    '${FLAT_FILE_NAME}',
    '${LOG_FILE_NAME}',
    '${BAD_FILE_NAME}',
    ${TOTAL_RECDS},
    ${LOAD_RECDS},
    ${BAD_RECDS}
    );
    commit;
    exit;
EOF

  echo "" >> $EMAIL_CONTENT
  echo "Load file: " ${FLAT_FILE_NAME} >> $EMAIL_CONTENT
  echo "Began at: " ${START_DATE} >> $EMAIL_CONTENT
  echo "Ended at: " ${END_DATE} >> $EMAIL_CONTENT
  echo "Total records: "  ${TOTAL_RECDS} >> $EMAIL_CONTENT
  echo "Loaded records: " ${LOAD_RECDS} >>  $EMAIL_CONTENT
  echo "Rejected records: "  ${BAD_RECDS} >>  $EMAIL_CONTENT
  echo "Log file: " ${LOG_FILE_NAME} >> $EMAIL_CONTENT
  echo "Bad file: " ${BAD_FILE_NAME} >> $EMAIL_CONTENT


  mv ./in/${FILE_TYPE}/${FILE_NAME}.txt ./inbak/${FILE_TYPE}/${FILE_NAME}.txt
  mv ./in/${FILE_TYPE}/${FILE_NAME}.ctl ./inbak/${FILE_TYPE}/${FILE_NAME}.ctl
 
}
  mail $EMAIL1 $EMAIL2<  From: NAD_loading_program
  Subject: NAD system data load log information for $FILE_TYPE
  `cat $EMAIL_CONTENT`
EOF

  rm  $EMAIL_CONTENT

}

######################
# Main
######################

if [ $# -lt 1 ]; then
  echo "Usage: ongoing SOURCE_TYPE"
  exit 1
fi
FILE_TYPE=$1
nad_env

if [ ${FILE_TYPE} == 'ORDER' ]; then
  nad_ftp  $1
fi

nad_ldr  $1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-778461/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-778461/