################################################
# input : userid, querySQL, outfile, colseq[default'|']
# output: outfile
################################################

#step 1.
if [ $# -eq 3 ];then
    userid=$1/$USINGDB
    querySQL=$2
    outfile=$3
# colsep='|'
 colsep=''
elif [ $# -eq 4 ];then
    userid=$1/$USINGDB
    querySQL=$2
    outfile=$3
 colsep=$4
else
    echo "Check input parameters please!"
    echo "Usage: $0 userid querySQL outfile colseq[default'|']"
    echo "Usage: userid user/passwd@sid"
    exit -1
fi

#step 2.
SQLSCRIPT=$HOME/tmp/unloadDataOnORA_$$.sql
>$SQLSCRIPT
echo "set colsep '${colsep}';" >> $SQLSCRIPT              #确定列分隔符(默认 = “ ”)
echo "set echo off;" >> $SQLSCRIPT                                #确定脚本中已执行的命令是否显示
echo "set feedback off;" >> $SQLSCRIPT                        #确定 SQL 语句受影响行数是否显示
echo "set heading off;" >> $SQLSCRIPT                          #确定在结果集列上面的标题是否显示
echo "set pagesize 0;" >> $SQLSCRIPT                          #确定在标题被重复以前的结果集的显示行数
echo "set linesize 10000;" >> $SQLSCRIPT                   #设置每行显示的字符数
echo "set wrap off;" >> $SQLSCRIPT                               #是关闭自动换行
echo "set numwidth 12;" >> $SQLSCRIPT                      #确定不带精度的数字列的最大显示宽度
echo "set termout off;" >> $SQLSCRIPT                          #确定已运行的 SQL 语句的输出是否显示
echo "set timing off;" >> $SQLSCRIPT                             #确定关于已运行的 SQL 语句的计时信息是否显示
echo "set trimout on;" >> $SQLSCRIPT                           #去除标准输出每行的拖尾空格
echo "set trims on;" >> $SQLSCRIPT                              #
echo "set trimspool on;" >> $SQLSCRIPT                      #去除重定向（spool）输出每行的拖尾空格
#echo "set numf 99999999999990.99 ; " >> $SQLSCRIPT
echo "spool ${outfile}.$$;" >> $SQLSCRIPT
echo "${querySQL};" >> $SQLSCRIPT
echo "spool off;" >> $SQLSCRIPT
echo "quit;" >> $SQLSCRIPT                                             #退出正在运行的脚本或关闭命令窗口

#step 3.
echo "Exporting..."
sqlplus -S ${userid} @${SQLSCRIPT}

if [ $? -ne 0 ]
then
    echo "Error: sqlplus -S ${userid} @${SQLSCRIPT} error."
    echo "Unload data to ${outfile} error, please check it!"
    exit -1;
fi

#mv ${outfile}.$$ ${outfile}
#awk '{print $0""}' ${outfile}.$$ | sed 's/ //g'  > ${outfile}
awk '{print $0,STR}' STR=${colsep} ${outfile}.$$ | sed 's/ *//g'  > ${outfile}
#awk '{print $0,STR}' STR=${colsep} ${outfile}.$$ | sed 's/ |$/|/g'  > ${outfile}
rm ${outfile}.$$

#step 4.
if [[ `grep "ORA-" ${outfile}` = "" ]]
then
 echo "Unload data to ${outfile} ......\t\t\t OK!!!"
else
 cat ${outfile}

    echo "Unload data to ${outfile} error, please check it!"
 exit -1;
fi

rm -f $SQLSCRIPT

echo "Unload complete! Thank you!"
exit 0;