load.txt

Unix环境下用Shell编写的Oracle数据库数据批量导入导出程序

USAGE="usage : $0 -u user/passwd -t tablename [-f path] [-a] [-p sepchar]"

INFILE="./myload.txt"

uflag=0
tflag=0
aflag=0
fflag=0
SEPCHAR='|'

while getopts :au:t:f:p: i
do
	case $i in
		a )
			aflag=1;;
		u )
			uflag=`expr $uflag + 1`
			USER=$OPTARG;;
		t )
			tflag=`expr $tflag + 1`
			TABLE=$OPTARG
			if [ $fflag -ne 1 ]
			then
				INFILE=${OPTARG}.txt
			fi
			;;
		f )
			fflag=`expr $fflag + 1`
			INFILE=$OPTARG;;
		p )
			SEPCHAR=$OPTARG;;
		? )
			echo $USAGE
			exit 2;;
	esac
done

if [ $uflag -ne 1 ]
then
	echo $USAGE
	exit 2
fi

if [ $tflag -ne 1 ]
then
	echo $USAGE
	exit 2
fi

sqlplus $USER<<! >/tmp/$TABLE.desc
desc $TABLE
!

echo LOAD DATA >/tmp/$TABLE.ctl
echo INFILE \'$INFILE\' >>/tmp/$TABLE.ctl
echo BADFILE \'$TABLE.bad\' >>/tmp/$TABLE.ctl
echo DISCARDFILE \'$TABLE.dsc\' >>/tmp/$TABLE.ctl
echo DISCARDMAX 50 >>/tmp/$TABLE.ctl
if [ $aflag -eq 1 ]
then
	echo INTO TABLE $TABLE TRUNCATE >>/tmp/$TABLE.ctl
else
	echo INTO TABLE $TABLE APPEND >>/tmp/$TABLE.ctl
fi
echo FIELDS TERMINATED BY \'$SEPCHAR\' >>/tmp/$TABLE.ctl
echo '(' >>/tmp/$TABLE.ctl

flag=0
iflag=0

sed -e'/^$/d' /tmp/$TABLE.desc|while read line 
do
	head=`expr "$line" : "\(....\).*"`
	if [ $head = "SQL>" ]
	then 
		if [ $flag -eq 0 ]
		then
			flag=4
		else
			break
		fi	
	fi

	if [ $flag -gt 1 ]
	then
		iflag=1;
		flag=`expr $flag - 1`
	fi
	

	if [ $flag -eq 1 ]
	then
		if [ $iflag -ne 0 ]
		then 
			iflag=0
		else
			echo ,
		fi
		
		head=`expr "$line" : "^[ ]*\([A-Z_][A-Z_0-9]*\).*"`
		echo $head
	fi
done >>/tmp/$TABLE.ctl
echo ')' >>/tmp/$TABLE.ctl

sqlldr $USER /tmp/$TABLE.ctl

#rm /tmp/$TABLE.desc
#rm /tmp/$TABLE.ctl