SqLoader实例

下面是Shell脚本文件

#!/usr/bin/ksh
#=============================================================================+
# FILENAME
#   /PRD/app/PT8.49/tsc_intf/HCMPRD/bin/tsc_upload_coa.sh
#
#  DESCRIPTION
#  Upload OFI GL account master (COA) into PS_TSC_GL_ACC_MST table
#
# MODIFIED
# 
#
# ========================================================================
# 应用psoft用户的的配置文件，里买包含TSC_INTF的路径定义
. /home/psoft/.profile

# 指定读取数据库的用户名密码
SQL_USER_PASS=sysadm/tesco1d@HCMPRD
FILE_TIME=`date +"%Y%m%d%H%M"`

FILE_DATE=`date +"%Y%m%d"`

export intf_dir=$TSC_INTF/HCMPRD/COA
# 数据文件格式 gl_coa_20091223.dat
export file_name=gl_coa_
# 上传到PeopleSoft此表中
export ctl_file_name=PS_TSC_GL_ACC_MST

process_dir=$intf_dir/process
history_dir=$intf_dir/history
ldr_data=$intf_dir/in_tray/$file_name$FILE_DATE.dat
ldr_process=$intf_dir/process/$file_name$FILE_DATE.dat
ldr_log=$intf_dir/log/$ctl_file_name$FILE_DATE.log
ldr_bad=$process_dir/$file_name$FILE_DATE.bad
ldr_history=$history_dir/$file_name$FILE_DATE.dat

# if there is the file name in the log folder, then delete the log file
if [ -r $ldr_log ]
then
rm $ldr_log
fi

if [ -r $ldr_bad ]
then
rm $ldr_bad
fi

if [ -r $ldr_process ]
then
rm $ldr_process
fi

# if there is flat data file, then move to process folder
if [ -e $ldr_data ]
then
  echo $ldr_data 
  mv $ldr_data $ldr_process
else
  echo $ldr_data >>$ldr_log
  echo "There is no interface data file to be loaded into PS_TSC_GL_ACC_MST table" >>$ldr_log
  exit 1
fi

# 调用SqLoader
sqlldr userid=$SQL_USER_PASS control=$intf_dir/bin/$ctl_file_name.ctl data=$ldr_process log=$ldr_log bad=$ldr_bad

if [ -r $ldr_bad ]
then  echo "Error!!! check the log file: "$ldr_log
  exit 1
else
  mv $ldr_process $ldr_history
  echo "Successful completion"
fi

 

下面是引用的Control File:

OPTIONS(SKIP=0)
Load DATA
CHARACTERSET ZHS16GBK
TRUNCATE INTO table PS_TSC_GL_ACC_MST
FIELDS TERMINATED BY '~'
OPTIONALLY ENCLOSED BY '"'
TRAILING  NULLCOLS 
(
TSC_SEG_NO,
TSC_VALUE_SET,
TSC_FLEX_VALUE,
TSC_DESCRIPTION,
TSC_MEANING,
ENABLED,
START_DATE   Date 'YYYYMMDD',
END_DATE   Date 'YYYYMMDD',
DATE_CREATED   Date 'YYYYMMDD',
LAST_UPDATE_DATE  Date 'YYYYMMDD',
TSC_VALUE_CAT   constant ' ',
TSC_ATTRIBUTE1   constant ' ',
TSC_ATTRIBUTE2   constant ' ',
TSC_ATTRIBUTE3   constant ' '
)