sqlplus sqlload

sqlload脚本

#!/bin/sh
clear
>test.log
if [-f custNos.bad ];then
	rm custNos.bad
fi
if [-f custNos.log ];then
	rm custNos.log
fi

# 提示输入数据库的用户名和密码
serviceName = "myoracle_s"
userName = "oracleuser"
userPwd = ''
echo "请输入 ORACLE 数据库用户名 $userName 密码: "
stty -echo
read userPwd
stty echo

echo "测试数据库连接..."
echo "连接信息：[service:$serviceName],[user:$userName],[password:******]"
echo "`sqlplus $userName/$userPwd@$serviceName <<EOF
select 1 from dual;
quit
EOF`" >db_conn.log
if [-n "`grep 'ERROR' db_conn.log`"]
then
echo "连接失败,操作中断,错误信息如下："
cat db_conn.log
exit
else
echo "连接成功,操作继续..."
fi
rm db_conn.log

echo "<STEP 1> 从交换平台取数据文件..."|tee -a test.log
if[-f cust.txt ];then
	echo "<STEP 1.1> 文件cust.txt 已存在"|tee -a test.log
else
	. /test/bin/key.sh
	host = '11.11.11.11'
	ftpuser = "test"
	ftppasswd = "111"
	file_dir = ./BDPF/temp
	local_dir = `pwd`
	
echo "`ftp -nv <<FTPEND
open $host
user $ftpuser $ftppasswd
binary
prompt off

cd $file_dir
lcd $local_dir
get cust.txt
bye
FTPEND`">>test.log

if[-f cust.txt ];then
	echo "<STEP 1.2> 文件cust.txt 已获取"|tee -a test.log
 else
 	echo "<STEP 1.3> 文件cust.txt 获取失败,请检查日志：test.log"|tee -a test.log
 	exit 0;
 fi
fi

echo "<STEP 1> 正在创建临时表.."|tee -a test.log
echo "`sqlplus $userName/$userPwd@$serviceName <<EOF
create table custNos(
customerno varchar2(10),
industryLevel varchar2(20)
);
quit
EOF`" >>test.log

echo "<STEP 2> 创建ctl文件..."|tee -a test.log
echo "load data
infile cust.txt
truncate into table custNos
fields terminated by '\!^'
trailing nullcols
(
customerNo,
industryLevel
)" > custNos.ctl

echo "<STEP 3> 正在导入数据至临时表..."|tee -a test.log
sqlldr $userName/$userPwd@$serviceName control=custNos.ctl log=./custNos.log bad=./custNos.bad readsize=20971520 direct=y errors=1000 rows=2000>>./custNos.log
cat custNos.log >>test.log

echo "<STEP 4> 正在校验导入结果..."|tee -a test.log
if [-n "`grep -E 'Msg|error|fail' custNos.log`"]
then
	echo"<STEP 4.1> 导入失败,导入明细请参考导入日志： "
	cat custNos.bad
	rm custNos.log
	echo "<STEP 6.1> 删除临时表..."|tee -a test.log
	echo "`sqlplus $userName/$userPwd@$serverName <<EOF
	drop table custNos;
	quit
	EOF`" >>test.log
	exit 0;
else
	echo "<STEP 4.2 导入成功>"|tee -a test.log
fi
rm custNos.log

echo "<STEP 5 更新客户号...> " tee -a test.log
echo "`sqlplus $userName/$userPwd@$serverName << EOF
     update table;
     commit;
     quit
     EOF`"|tee -a test.log
     
echo "<STEP 6.2> 删除临时表"|tee -a test.log
echo "`sqlplus $userName/$userPwd@$serverName << EOF
		drop table custNos;
		quit
		EOF`" >>test.log

echo "<END> 更新完成(时间：$(date " +%Y%m%d %H:%M:%S"))"|tee -a test.log

exit 0