自动sqlldr导入脚本

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

#!/bin/bash

exportORACLE_BASE=/home/db/oracle/oracledb

exportORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

exportORACLE_SID=BTOG

exportPATH=$ORACLE_HOME/bin:$PATH

exportNLS_LANG="SIMPLIFIEDCHINESE_CHINA".AL32UTF8

username=bimp

password=bimp

active_logfile_datadir=/opt/b2gbak216/.test/active_log_file

logfile_prefix_name=active_log

insert_file_log(){

localfile_name=$1

localoperator_code=`

sqlplus-S/nolog<<EOF

conn$username/$password

setheadingofffeedbackoff

varout_file_idnumber;

callfile_log_insert('$file_name',:out_file_id);

select:out_file_idfromdual;

quit

EOF`

echo$operator_code

}

update_file_log(){

localfile_id=$1

localoperate_status=$2

localfile_data=$3

localoperator_code=`

sqlplus-S/nolog<<EOF

conn$username/$password

setheadingofffeedbackoff

varout_result_codenumber;

callfile_log_update($file_id,'$operate_status','$file_data',:out_result_code);

select:out_result_codefromdual;

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`

fordata_filein$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_loadall

customer_tran_file_load(){

localdatadir=/home/db/oracle/oracledb/flash_recovery_area/customer_tran

localcontrol_ctl=/home/db/oracle/oracledb/flash_recovery_area/sqlldr/customer_tran/customer_tran.ctl

localfile_prefix_name=TRAN_110_

localfile_postfix_name=txt

localall_data_file=`ls$datadir|grep"$file_postfix_name$"`

fordata_filein$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`

fordata_filein$all_data_file;do

centrol_file_load$active_logfile_datadir/$data_file$active_log_file_ctl$logfile_prefix_name$file_postfix_name

done

}

#manualloadonefile,thefilepathisabsolutepath

manual_file_load(){

localdata_file

localcontrol_file

localfile_prefix_name

localfile_postfix_name

echo-n"pleaseinputthedatafilename(absolutepath):"

readdata_file

echo-n"pleaseinputthecontrolfilename:"

readcontrol_file

echo-n"pleaseinputthedatafileprefixname[]:"

readfile_prefix_name

echo-n"pleaseinputthedatafilepostfixname[]:"

readfile_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"callcentrol_file_loadfunctionyoumustinputthedatafilename"

exit1

fi

if[-z$2];then

echo"callcentrol_file_loadfunctionyoumustinputthecontrolfilename"

exit1

fi

if[-z$3];then

echo"callcentrol_file_loadfunctionyoumustinputthedatafile'sprefixname"

exit1

fi

if[-z$4];then

echo"callcentrol_file_loadfunctionyoumustinputthedatafile'spostfixname"

exit1

fi

locall_data_file=$1

locall_control_file=$2

locall_file_prefix_name=$3

locall_file_postfix_name=$4

locall_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.insertthedatafiletodatabasea

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.sqlloadreaddatafiledatatodatabase

load_data$l_data_file$l_control_file

#3.ifsqlloadthedatasuccess,updatethefileoperate_statusfromthedatabase

if[-f"$l_data_file.bad"];then

update_file_log$v_file_id0${l_simple_data_file_name:${#l_file_prefix_name}:8}

else

update_file_log$v_file_id2${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_id0${l_simple_data_file_name:${#l_file_prefix_name}:8}

echo"fileloaderrors,becausethethecontrolfileisnotexistsorcontrolfileisemptyorcontrolfileerrors"

fi

else

echo"fileloaderrors,becausethefilename$l_data_filehavebeinthedatabaseordatabaseerror"

fi

else

echo"datafileloaderrors,thedatafilepostfixname${l_simple_data_file_name:0:${#l_file_prefix_name}}andl_file_postfix_nameparamvalueis$l_file_postfix_namenotequal"

fi

else

echo"datafileloaderrors,thedatafileprefixname${l_simple_data_file_name##*.}andl_file_prefix_nameparamvalueis$l_file_postfix_namenotequal"

fi

else

echo"datafileloaderrors,becausethedatafile$l_data_fileisnotexistsordatafileisempty"

fi

}

load_data(){

if[-z$1];then

echo"callload_datafunctionyoumustinputthedatafilename"

exit1

fi

if[-z$2];then

echo"thecontrol_fileparamisneed"

exit1

fi

if[-f$1]&&[-f$2];then

localfile_name=$1

localcontrol_file=$2

sqlldr$username/$passwordCONTROL="${control_file}"DATA="${file_name}"BAD="${file_name}.bad"DISCARD="${file_name}.disc"LOG="${file_name}.log"ERRORS=100DIRECT=trueSKIP=1rows=10000

else

echo"thedata_file$file_nameorcontrol_file$control_fileisnotexists"

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}"

exit1

;;

esac

exit0