Oracle sqlload 脚本

#!/bin/bash
##################
#@author: chichuduxing
#@date: 20160913
##################

#加载环境变量(需要加载oracle的环境变量,根据安装情况自己调整)

if [ -f /etc/profile.d/oraenv.sh ];then
        source /etc/profile.d/oraenv.sh
fi

#打印帮助信息
function showhelp()
{
	#有特殊字符的时候一定要用单引号括起来
	echo -e "\033[32musage: sh `basename $0` [-c 'uname/passwd@ip:port/sid'] [-t 'tablename'] [-e 'field1,field2'] [-f 'filename'] [-s 'splitchar']\033[0m"
	echo -e "\033[31mevery param can not empty, please check!\033[0m"
}

while getopts c:e:f:s:t: OPT; do
	case $OPT in
		c|+c)
			dbconn="$OPTARG"
			;;
		t|+t)
			tablename="$OPTARG"
			;;
		e|+e)
			fields="$OPTARG"
			;;
		f|+f)
			filename="$OPTARG"
			;;
		s|+)
			splitchar="$OPTARG"
			;;
		*)
			showhelp
			exit 1
	esac
done

#检查oracle环境
function checkenv()
{
	if [ -z "$ORACLE_HOME" ] ;then
		echo -e "\033[31mORACLE_HOME is empty! please install oracle client!\033[0m"
		exit 2
	fi
}

#检查用户输入的变量
function checkvalue()
{
	if [ "" = "$dbconn" -o "" = "$fields" -o "" = "$filename" -o "" = "$splitchar"  ] ;then
		showhelp
		exit 3
	fi
	
	if [ ! -e "$filename" -o ! -s "$filename" ] ;then
		echo "[${filename}] not exist or is empty!"
		exit 3
	fi
	
	echo "dos2unix [${filename}] now..."
	dos2unix ${filename} >/dev/null 2>&1
}

#对用户的输入进行转换
function transvalue()
{
	#生成文件名并清理
	ctlfilename="load_${tablename}.ctl"
	logfilename="load_${tablename}.log"
	badfilename="load_${tablename}.bad"
	
	if [ -e $ctlfilename ];
		then 
		rm -f $ctlfilename 
	fi
	if [ -e $logfilename ];
		then
		rm -f $logfilename 
		fi
	if [ -e $badfilename ];
		then
		 rm -f $badfilename 
	fi
	
	#对字段增加char(4000)
	sourcefields=$fields
	temp=${fields//,/ }
	fields="";
	for onefield in $temp
		do 
		if [ -z  "${fields}" ] ;then
			fields="${onefield} char(4000)"
		else
			fields="${fields}, ${onefield} char(4000)"
		fi
	done
	echo -e "\033[33mTrans FIELDS [${sourcefields}] to [$fields]\033[0m"

	#对分隔符进行处理，避免输入的时候没有转义或者没有使用单引号
	sourcesplitchar=$splitchar
	if [ "t" = "$splitchar" ] ;then
		#处理\t
		echo -e "\033[33mTrans SPLITCHAR [${splitchar}] to [\\\t]\033[0m"
		splitchar="\t"
	elif [ "u" = "${splitchar:0:1}" ] ;then
		#处理\uxxxx
		echo -e "\033[33mTrans SPLITCHAR [${splitchar}] to [\\${splitchar}]\033[0m"
		splitchar="\\${splitchar}"
	fi
}

#打印转换后的用户输入
function showinfo()
{
	echo -e "\033[34m##################show info##################\033[0m"
	echo -e "\033[34mDBCONN :[${dbconn}]\033[0m"
	echo -e "\033[34mTABLENAME :[${tablename}]\033[0m"
	echo -e "\033[34mFIELDS :[${fields}]\033[0m"
	echo -e "\033[34mSOURCE FIELDS:[${sourcefields}]\033[0m"
	echo -e "\033[34mFILENAME :[${filename}]\033[0m"
	echo -e "\033[34mSPLITCHAR :[${splitchar}] SOURCE VALUE:[${sourcesplitchar}]\033[0m"
	echo -e "\033[34mCTLFILENAME :[${ctlfilename}]\033[0m"
	echo -e "\033[34mLOGFILENAME :[${logfilename}]\033[0m"
	echo -e "\033[34mBADFILENAME :[${badfilename}]\033[0m"
	echo -e "\033[34m##################show info##################\033[0m"
}

function confirm()
{
	read -p "is info ok? [yes/no] :" input
	while [ ${input} != "yes" -a ${input} != "no" ]
	do
		read -p "input error,please write [yes/no] :" input
	done
	
	if [ $input = "no" ];then
		echo -e "\033[33m*************** script exit ***************\033[0m"
		exit 7
	fi
}

#生成ctl文件
function writectl()
{
	echo "LOAD DATA" > $ctlfilename
	echo "INFILE '$filename'" >> $ctlfilename
	echo "append into table $tablename" >> $ctlfilename
	echo "fields terminated by '$splitchar' TRAILING NULLCOLS" >> $ctlfilename
	echo "($fields)" >> $ctlfilename
	
	if [ ! -e "$ctlfilename" -o ! -s "$ctlfilename" ] ;then
		echo "write ${ctlfilename} failed! ${ctlfilename} not exist or is empty ,please check!"
		exit 4
	else
		echo "------write ${ctlfilename} done!-----"
		cat $ctlfilename
		echo "---------------------------------------------"
	fi
}

#执行sqlload入库
function sqlload()
{

	echo "Load command :$ORACLE_HOME/bin/sqlldr userid=$dbconn control=$ctlfilename log=$logfilename bad=$badfilename readsize=10240000 bindsize=10240000 errors=100000 direct=false"
	
	$ORACLE_HOME/bin/sqlldr userid=$dbconn control=$ctlfilename log=$logfilename bad=$badfilename readsize=10240000 bindsize=10240000 errors=100000 direct=false >/dev/null 
	
	if [ $? -ne 0 ] ;then
		echo -e "\033[31msqlldr failed ,please check the sqlldr command!\033[0m"
		exit 5
	fi
	
	if [ -e "${badfilename}" ] ;then
		echo -e "\033[31mload failed ,please read ${badfilename} and ${logfilename}\033[0m"
		exit 6
	else
		echo -e "\033[32m########## load ${filename} to table ${tablename} done! more info in ${logfilename} ##########\033[0m"
	fi
}


function main()
{
	checkenv

	checkvalue

	transvalue

	showinfo

	writectl

	confirm

	sqlload
}


main

使用样例:

sh sqlload.sh -c 'scott/ttocs@192.168.1.200:1521/ora11g' -t 'tb_load_test' -e 'userid,username,timestamp' -f 'user.txt' -s '\t'