#!/bin/ksh
#############################################################################
#功能：
# 将数据库中某张表的内容导出成为一个文本格式,每条记录一行
#配置：
# 参见当前目录下unload.ctl文件
#用法：
# sqlunldr userid/passwd[@connection] table_name
#注意：
# 如果表中某域为char(n)，包含\n,\r等将会造成导出的数据行数比实际记录数多
#
#############################################################################
getConfig()
{
if [ -f "./unload.ctl" ];then
. ./unload.ctl
echo "Begin reading unload.ctl."
echo =============================
echo "sep=$sep"
echo "fields=$fields"
#`echo $fields|sed -e "s/ //g" `
echo "destDir=$destDir"
echo "where=$where "
echo "order=$order "
echo =============================
echo "Reading unload.ctl complete."
echo ""
return 0
else
return 1
fi
}
getConfig
if [ $? -ne 0 ];then
echo "Error: Can not find unload.ctl file.Please check it!"
exit 1
fi
if [ "X$1" = "X" ];
then
echo "Usage: $0 <userid/passwd@connection> <table_name>"
# exit
echo \c "Userid:"
read userid1
echo \c "Passwd:"
echo off
read passwd
userid=$userid1$passwd
echo on
else
userid=$1
fi
if [ "X$2" = "X" ]; then
echo "No table in user $userid."
echo "Usage: $0 <userid/passwd@connection> <table_name>"
exit
else
table=$2
fi
#selectSql=`echo "select $fields from $table" |sed -e "s/,/||\',\'||/g"`
#echo selectSql=$selectSql
echo "Begin to unload... please wait..."
sqlplus $userid <<! >/dev/null
set COLSEP $sep;
set echo off;
set heading off ;
set feedback off;
set pagesize 0 ;
set linesize 1000;
set termout off;
set trimout on;
set trimspool on;
spool wk_${table}.txt;
select $fields from $table $where $order;
spool off;
/
exit
/
!
if [ "$?" -ne 0 ] ;
then
echo "Error:sqlplus $userid error in unload table $table!! "
echo "Please check userid and passwd or database."
exit
fi
cat wk_${table}.txt | sed -e '/^SQL>/d' -e '/^$/d' >$table.txt
if [[ `grep "ORA\-" ${table}.txt` = "" ]];
then
cat $table.txt|sed -e 's/[[:space:]]//g'>wk_${table}.txt
else
cat $table.txt
err="$table"
fi
if [[ "X$err" = "X" ]];then
echo "Unload table $table complete! "
else
echo "Unload table $err error, please check it!"
exit
fi
echo "Check the correctness..."
cnt1=`sqlplus $userid<<! >/dev/null
set echo off;
set heading off ;
set feedback off;
set pagesize 0 ;
set termout off;
set trimout on;
set trimspool on;
spool cnt1.txt;
select count(*) from $table $where;
spool off;
exit;
/
<<! `
cnt1=`cat cnt1.txt|sed -e '/^SQL>/d' -e '/^$/d' -e 's/ //g' `
cnt2=`wc -l ${table}.txt |awk '{print($1)}'`
#echo "\$cnt1=$cnt1"
#echo "\$cnt2=$cnt2"
echo "表中记录=$cnt1 条,生成的文件行数=$cnt2 ."
if [ $cnt1 -eq $cnt2 ];
then
echo "Congratulations,unload successful!"
else
echo "Error:导出记录数与表中记录数不等,请检查表中是否有类型为char(n)的某个域的数据含有\n."
fi
mv wk_$table.txt $table.txt
rm -f cnt1.txt
############****************************************配置文件
#file:unload.ctl
#分隔符
sep=","
#输出字段,不能有空格连接;如果有空格需将=右边用""引起
fields="ORG_BIND_ACC_NBR,ACCT_MONTH_ID,ACCT_CYCLE_TMPL_ID,ACCT_CYCLE_ID,PARTITION_ID,SOURCE_TYPE,BUSINESS_KEY,CALL_TYPE,ORG_ACC_NBR_A,ORG_ACC_NBR_B,START_DATE,START_TIME,CALL_DURATION,END_DATE,END_TIME,TOTAL_METER_VALUE,PURE_METER_VALUE,SWITCH_AREA_CODE,SWITCH_ID,ORG_FILE_NAME,CDR_POS_IN_FILE,TRUNK_IN,TRUNK_OUT,CARD_NO,CARD_TYPE,IS_PREFEE,ORG_FEE1+ORG_FEE2+ORG_FEE3 ORG_FEE,VISIT_AREA_CODE_A,SWITCH_TYPE,BILLING_TYPE,OPER_SEQUENCE"
#条件,没有条件为空
where="where ACCT_MONTH_ID>='200410'"
#排序字段,没有条件为空
order= #"order by start_date"
#输出文件存放目录
destDir=.
