#!/bin/bash
#script load.sh
#author marco xi
#created 2011/01/06
#version v1.0
####################################################################################################

#初始化参数
userid="scott/tiger@pdb"
logdir=./"log"
loadlog=./"load.log"
load_ind='R'
colsep="'|'"
time_stamp=`date "+%Y-%m-%d-%H:%M:%S"`

#日志函数
writelog()
{
echo "${time_stamp} $1" | tee -a $loadlog
}

#产生控制文件
make_ctl_file()
{
local p_infile=$1
local p_table=$2
local p_load_ind=$3
local p_colsep=$4

#处理装载类型
local p_load_typ="REPLACE"
case $p_load_ind in
    r)p_load_typ="REPLACE" #先清空表再装载
      ;;
    R)p_load_typ="REPLACE" #先清空表再装载
      ;;
    i)p_load_typ="INSERT" #给空表装载（如果表中有记录则退出）
      ;;
    I)p_load_typ="INSERT" #给空表装载（如果表中有记录则退出）
      ;;
    a)p_load_typ="APPEND" #在原表数据基础上插入
      ;;
    A)p_load_typ="APPEND" #在原表数据基础上插入
      ;;
    *)p_load_typ="REPLACE" #先清空表再装载数据
      ;;
esac

local p_ctlfile="${logdir}/${table}_${time_stamp}.ctl"
local p_tab_desc="${logdir}/${table}_${time_stamp}.desc"

#获取表结构
sqlplus $userid </dev/null
set echo off;
set heading off;
set feedback off;
set pagesize 0;
set linesize 100;
set termout off;
set trimout on ;
set trimspool on;
spool $p_tab_desc;
desc $p_table;
spool off;
exit
EOF

#检查是否成功连接数据库
if [ $? -ne 0 ]; then
     writelog "Error: $usrid is not correct, please check it!"
     exit 1
fi

#检查表是否存在
local p_err_msg=`head -5 $p_tab_desc | grep "object $p_table does not exist"`
if [ "${p_err_msg}OK" != "OK" ]; then
     writelog "Error: ${p_err_msg}!"
     exit 1
fi

#产生控制文件
if [ -f $p_tab_desc ]; then
     local p_num=`wc -l $p_tab_desc | awk '{print $1}'`
     echo "LOAD DATA INFILE $p_infile $p_load_typ INTO TABLE $p_table FIELDS TERMINATED BY $p_colsep TRAILING NULLCOLS (" > $p_ctlfile
     sed -e '1,3d' -e '/SQL>/d' -e '/^$/d' $p_tab_desc |awk -v num=`expr $p_num - 5` '{print (NR < num ? $1"," : $1"\n)")}' >> $p_ctlfile
     rm -f $p_tab_desc
else
     writelog "Error: $p_tab_desc doesn't exist!"
     exit 1
fi
return 0
}

#主程序
case $# in
2) table=$1
     infile="${2}"
     ;;
3) table=$1
     infile="${2}"
     load_ind="'""{$3}""'"
     ;;
*) writelog "Error: the usage of load.sh is not correct!"
     writelog "Usage: sh load.sh table infile [load_type]"
     exit 1
     ;;
esac

ctlfile="${logdir}/${table}_${time_stamp}.ctl"
logfile="${logdir}/${table}_${time_stamp}.log"
badfile="${logdir}/${table}_${time_stamp}.bad"
dscfile="${logdir}/${table}_${time_stamp}.dsc"

#检查数据文件
if [ -f $infile ]; then
   writelog "Note: there are "`wc -l $infile | awk '{print $1 }'`" rows in ${infile}."
else
   writelog "Error: $infile doesn't exist!"
   exit 1
fi

#产生控制文件
make_ctl_file $infile $table $load_ind $colsep

#检查控制文件是否产生
if [ $? -eq 0 ] && [ -f $ctlfile ]; then
   writelog "Note: the control file is $ctlfile."
else
   writelog "Error: Making $ctlfile failed!"
   exit 1
fi

#装载数据
echo sqlldr $userid \
     control=$ctlfile \
     log=$logfile \
     bad=$badfile \
     discard=$dscfile \
     direct=true \
     > ./"load_${table}_${time_stamp}".sh
sqlldr $userid \
       control=$ctlfile \
       log=$logfile \
       bad=$badfile \
       discard=$dscfile \
       direct=true \
       >/dev/null

#核查装数结果
rtn="$?"
if [ $rtn -ne 0 ]; then
   writelog "Error: Loading data failed!"
   exit 1
else
   writelog "Note:`grep "Rows successfully loaded" $logfile`"
   exit 0
fi

ITPub博客

数据装载脚本

最新文章