自动sqlldr导入脚本

自己写的通用的sqlldr导入脚本，配置在crontab 里面，每天自动导入数据:

#!/bin/bash
export ORACLE_BASE=/home/db/oracle/oracledb
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=BTOG
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG="SIMPLIFIED CHINESE_CHINA".AL32UTF8
username=bimp
password=bimp
active_logfile_datadir=/opt/b2gbak216/.test/active_log_file
logfile_prefix_name=active_log
active_log_file_ctl=

insert_file_log(){
local file_name=$1
local operator_code=`
sqlplus -S /nolog <<EOF
conn $username/$password
set heading off feedback off
var out_file_id number;
call file_log_insert('$file_name',:out_file_id);
select :out_file_id from dual;
quit
EOF`
echo $operator_code
}
update_file_log(){
local file_id=$1
local operate_status=$2
local file_data=$3
local operator_code=`
sqlplus -S /nolog <<EOF
conn $username/$password
set heading off feedback off
var out_result_code number;
call file_log_update($file_id,'$operate_status','$file_data',:out_result_code);
select :out_result_code from dual;
EOF`
echo $operator_code
}

file_load(){

local data_child_dir=`ls -t $datadir`

for data_dir in $data_child_dir;do
if [ -d $data_dir ];then
all_data_file=`ls $data_dir`
for data_file in $all_data_file;do
centrol_file_load $datadir/$data_dir/$data_file $centrol_ctl $file_prefix_name $file_postfix_name
done
if [ -z $1 ];then
exit
fi
fi

done
}
file_load_all(){
file_load all
}

customer_tran_file_load(){
local datadir=/home/db/oracle/oracledb/flash_recovery_area/customer_tran
local control_ctl=/home/db/oracle/oracledb/flash_recovery_area/sqlldr/customer_tran/customer_tran.ctl
local file_prefix_name=TRAN_110_
local file_postfix_name=txt

local all_data_file=`ls $datadir|grep "$file_postfix_name$"`
for data_file in $all_data_file;do
centrol_file_load $datadir/$data_file $control_ctl $file_prefix_name $file_postfix_name
done
}

active_log_file_load(){
all_data_file=`ls $active_logfile_datadir`
for data_file in $all_data_file;do
centrol_file_load $active_logfile_datadir/$data_file $active_log_file_ctl $logfile_prefix_name $file_postfix_name
done
}
# manual load one file,the file path is absolute path
manual_file_load(){
local data_file
local control_file
local file_prefix_name
local file_postfix_name
echo -n "please input the data file name(absolute path):"
read data_file
echo -n "please input the control file name:"
read control_file
echo -n "please input the data file prefix name[]:"
read file_prefix_name
echo -n "please input the data file postfix name[]:"
read file_postfix_name
if [ -z $file_prefix_name ];then
file_prefix_name=all
fi
if [ -z $file_postfix_name ];then
file_postfix_name=all
fi
centrol_file_load $data_file $control_file $file_prefix_name $file_postfix_name
}
centrol_file_load(){
if [ -z $1 ];then
echo "call centrol_file_load function you must input the data file name"
exit 1
fi
if [ -z $2 ];then
echo "call centrol_file_load function you must input the control file name"
exit 1
fi
if [ -z $3 ];then
echo "call centrol_file_load function you must input the data file's prefix name"
exit 1
fi
if [ -z $4 ];then
echo "call centrol_file_load function you must input the data file's postfix name"
exit 1
fi
local l_data_file=$1
local l_control_file=$2
local l_file_prefix_name=$3
local l_file_postfix_name=$4
local l_simple_data_file_name=`basename $l_data_file`

if [ -f "$l_data_file" ] && [ -s "$l_data_file" ];then
if [ ${l_simple_data_file_name:0:${#l_file_prefix_name}} == $l_file_prefix_name ] || [ $l_file_prefix_name == "all" ];then
if [ ${l_simple_data_file_name##*.} == $l_file_postfix_name ] || [ $l_file_postfix_name == "all" ];then
#1.insert the datafile to databasea
v_file_id=`insert_file_log $l_data_file`
if [ $v_file_id != "0" ];then
if [ $l_control_file != "" ] && [ -f "$l_control_file" ] && [ -s "$l_control_file" ];then
#2.sqlload read datafile data to database
load_data $l_data_file $l_control_file
#3.if sqlload the data success,update the file operate_status from the database
if [ -f "$l_data_file.bad" ];then
update_file_log $v_file_id 0 ${l_simple_data_file_name:${#l_file_prefix_name}:8}
else
update_file_log $v_file_id 2 ${l_simple_data_file_name:${#l_file_prefix_name}:8}
tar -czf $l_data_file.tar.gz $l_data_file
mv $l_data_file.tar.gz /home/db/oracle/oracledb/flash_recovery_area/data_archive/
rm -fr $l_data_file
fi
else
update_file_log $v_file_id 0 ${l_simple_data_file_name:${#l_file_prefix_name}:8}
echo "file load errors,because the the control file is not exists or control file is empty or control file errors"
fi
else
echo "file load errors,because the file name $l_data_file have be in the database or database error"
fi
else
echo "data file load errors,the data file postfix name ${l_simple_data_file_name:0:${#l_file_prefix_name}} and l_file_postfix_name param value is $l_file_postfix_name not equal"
fi
else
echo "data file load errors,the data file prefix name ${l_simple_data_file_name##*.} and l_file_prefix_name param value is $l_file_postfix_name not equal"
fi
else
echo "data file load errors,because the data file $l_data_file is not exists or data file is empty"
fi
}
load_data(){
if [ -z $1 ];then
echo "call load_data function you must input the data file name"
exit 1
fi
if [ -z $2 ];then
echo "the control_file param is need"
exit 1
fi
if [ -f $1 ] && [ -f $2 ];then
local file_name=$1
local control_file=$2
sqlldr $username/$password CONTROL="${control_file}" DATA="${file_name}" BAD="${file_name}.bad" DISCARD="${file_name}.disc" LOG="${file_name}.log" ERRORS=100 DIRECT=true SKIP=1 rows=10000
else
echo "the data_file $file_name or control_file $control_file is not exists"
fi
}

case "$1" in
customer_tran_file_load)
customer_tran_file_load
;;
active_log_file_load)
active_log_file_load
;;
manual_file_load)
manual_file_load
;;
insert_file_log)
insert_file_log
;;
update_file_log)
update_file_log
;;
load_data)
load_data
;;
*)
echo $"Usage: $0 {customer_tran_file_load|active_log_file_load|insert_file_log|update_file_log|load_data|active_log_file_load|manual_file_load}"
exit 1
;;
esac
exit 0