SHELL_调用java、sqlldr文件,下载txt加载至oracle并校验

1.shell调用java并作数据校验

#!/bin/sh

## 作者：付博
## 功能描述：用作调用java文件下载脚本，并加载至oracle数据库，组后作数据条数校验

#0.环境变量
java_path="/c/Users/Administrator/Desktop/jiefangdai_callout/java"
oralce_txt_path="/c/Users/Administrator/Desktop/jiefangdai_callout/sftp_file"
##加入环境变量
# export PATH=java_path:oralce_txt_path:$PATH

#1.下载前，查询日志表

result=$(sqlplus -s fb/fb@orcl <<eof

set heading off   
set pagesize 0

select count(1) from data_load_log
where STATUS_ID = (select max(STATUS_ID) from data_load_log) 
and PROJECT_STATUS in ('load start','loading','load succeed');
exit;

eof
)

echo $result  

#2.下载文件
if [ $result == 0 ]   # set heading off + set pagesize 0 让result直接为数字，所以可以直接跟0比较。不用在字符比较（因为result里面有很多空格，所以只能判断是否包含字符  #"$result" 字符比较）

then
java -jar $java_path/sfto_download.jar  #java下载文件 

#3.执行ctl文件
cmd_01="sqlldr userid=fb/fb@orcl control='C:\Users\Administrator\Desktop\jiefangdai_callout\sqlldr\control.ctl' log='C:\Users\Administrator\Desktop\jiefangdai_callout\sqlldr\load_data.log'"

# 3.1 插入日志
sqlplus fb/fb@orcl <<EOF
insert into data_load_log(TABLE_NAME,project_name,project_status,status_id,op_TIME)
values('table_test','load','load start',2,to_char(sysdate,'yyyy/mm/dd HH24:mi'));
commit;
EXIT;
EOF

# 3.2 运行sqlldr
{
{
result01=$(eval $cmd_01)
} && {
# 3.3 插入日志
sqlplus fb/fb@orcl <<EOF
insert into data_load_log(TABLE_NAME,project_name,project_status,status_id,op_TIME)
values('table_test','load','load succeed',3,to_char(sysdate,'yyyy/mm/dd HH24:mi'));
commit;
EXIT;
EOF
}
} || {
# 3.4 异常捕获
if [[ result01=~"  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 1 行 没有加载。" ]]; then
sqlplus fb/fb@orcl <<EOF
insert into data_load_log(TABLE_NAME,project_name,project_status,status_id,op_TIME)
values('table_test','load','load succeed',3,to_char(sysdate,'yyyy/mm/dd HH24:mi'));
commit;
EXIT;
EOF
else
sqlplus fb/fb@orcl <<EOF
insert into data_load_log(TABLE_NAME,project_name,project_status,status_id,op_TIME)
values('table_test','load','load failed',4,to_char(sysdate,'yyyy/mm/dd HH24:mi'));
commit;
EXIT;
EOF
fi
}

#4.数据验证
count_txt=$(grep -o '@^@' $oralce_txt_path/oracle.txt | wc -l)
echo $count_txt

count_oracle=$(sqlplus -s fb/fb@orcl <<eof

set heading off   
set pagesize 0

select count(1) from test_aaa;
exit;

eof
)
echo $count_oracle

if [ $count_txt = $count_oracle ]

then
sqlplus fb/fb@orcl <<EOF
insert into data_load_log(TABLE_NAME,project_name,project_status,status_id,op_TIME)
values('table_test','audit','audit match',0,to_char(sysdate,'yyyy/mm/dd HH24:mi'));
commit;
EXIT;
EOF
echo "数据匹配"
else

sqlplus fb/fb@orcl <<EOF
insert into data_load_log(TABLE_NAME,project_name,project_status,status_id,op_TIME)
values('table_test','audit','audit not match',1,to_char(sysdate,'yyyy/mm/dd HH24:mi'));
commit;
EXIT;
EOF
echo "数据不匹配"
fi

else
echo "no need load"  #no need load

fi