oracle表导出文本,将Oracle数据库中某张表的内容导出成文本格式文件

#!/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 "

# 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 "

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.txtrm -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=.