Unload table data to unl files (Oracle)

叶丹4610

#!/bin/ksh

###############################################################
##  Use    : Unload the table data to unl format
##  Usage  : Put the table names in tables.txt and call the script
###############################################################


############# Usage ##########################
if [ $# -ne 3 ]
then
echo "Usage: $0 username password oracleServiceName"
exit 1
fi


#set the parameters
username=$1
password=$2
servicename=$3

#Check the connection
sqlplus -S ${username}/${password}@${servicename} > connection.log <<EOF
EOF

isError=`grep -i "ERROR" connection.log | wc -l`

#If error exit the script
if [ $isError -ne 0 ]
then
echo "Oracle connection Failed!!! "
cat connection.log
rm -f connection.log
exit 1
fi


### Unload Function #########
unloadData() {
if [ $# -eq 0 ]
then
return
fi
sqlplus -S ${username}/${password}@${servicename} <<EOF >>/dev/null
set head off;
set feed off;
spool on;
spool result.txt;
${1}
spool off;
EOF

#Remove Blank Lines and SQL prompts from the output
awk 'NF!=0 && $0!~/SQL/ { print $0 }' result.txt > queryResult.txt
rm result.txt
return
}


#Check if tables.txt exist or not
if [ ! -f tables.txt ]
then
echo "tables.txt file does not exit. Please put the tablenames in tables.txt"
exit 1
fi

sed -i y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/ tables.txt

#Read the Tables
for tablename in `cat tables.txt`
do
#Get the columns
unloadData "select column_name from COLS where table_name = '${tablename}';"
mv queryResult.txt columnName.txt

sqlQuery=""

#For the SQLQuery to unload as unl
for columnname in `cat columnName.txt`
do
if [ "${sqlQuery}x" == "x" ]
then
sqlQuery="select ${columnname}"
else
sqlQuery="${sqlQuery}||'|'||${columnname}"
fi
done

sqlQuery="${sqlQuery} from ${tablename};"

unloadData "${sqlQuery}"

mv queryResult.txt ${tablename}.unl
done