這是一個自動從各資料來源利用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"`

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

自動ftp,生成control file，sql*load的shell指令碼

相關文章