#!/bin/bash
#script. name unload.sh
#author marco xi
#created 2011/01/05
#updated 2011/01/05
#version v2.0
##############################################################
#初始参数
logfile=./`basename $0`.log
usrid=""
#数据分隔符
colsep="|"
#初始化数据文件和SQL
p_file="UNKNOWN_"`date "+%Y-%m-%d %H:%M:%S"`.TXT
p_sql="select * from dual"
#日志处理函数
writelog()
{
echo `date "+%Y-%m-%d %H:%M:%S"` " $1" | tee -a $logfile
}
#帮助函数
help()
{
writelog "the usage of unload.sh: sh unload.sh -h|H|f|F|s|S|t|T data_file sql_file|sql|table [colsep]"
}
#处理帮助信息
if [ "$1" == "-h" ] || [ "$1" == "-H" ]; then
help
exit 0
fi
#检查参数个数
if [ "$#" -lt 3 ] || [ "$#" -gt 4 ]; then
writelog "Error: the number of parameters is not correct!"
help
exit 0
fi
case $1 in
-f) p_file="${2}"
p_sql="@${3}"
;;
-F) p_file="${2}"
p_sql="@${3}"
;;
-s) p_file="${2}"
p_sql="$3"
;;
-S) p_file="${2}"
p_sql="$3"
;;
-t) p_file="${2}"
p_sql="select * from ${3}"
;;
-T) p_file="${2}"
p_sql="select * from ${3}"
;;
*) writelog "Error: the usage of parameters is not correct!"
help
exit 0
;;
esac
if [ "$#" -eq 4 ]; then
colsep="$4"
fi
p_tmp_file="临时_${p_file}"
writelog "unloading data start ... ..."
sqlplus $usrid
set colsep $colsep;
set echo off;
set heading off;
set feedback off;
set pagesize 0;
set linesize 5000;
set termout off;
set trimout on ;
set trimspool on;
spool ${p_file};
${p_sql};
spool off;
exit
EOF
if [ "$?" -ne 0 ]; then
writelog "Error: $usrid is error!"
writelog "please check the username and password."
exit 0
fi
#错误提示
err_msg1=`tail -50 $p_file | grep "ERROR at line"`
err_msg2=`tail -50 $p_file | grep "ORA"`
if [ "${err_msg1}OK" != "OK" ]; then
writelog "Error: some errors occured in ${p_sql}"
writelog "${err_msg1}"
writelog "${err_msg2}"
exit 0
fi
writelog "begining to process the file ${p_file} ... ..."
sed -e '/SQL>/d' -e 's/ //g' $p_file > $p_tmp_file
rm -f $p_file
mv $p_tmp_file $p_file
data_rows=`wc -l $p_file | awk '{print $1}'`
writelog "export $data_rows rows"