如何创建Host Concurrent program读取csv文件中的数据到DataBase

一，创建表：

createtableXXUTS_HOST_CONCURRENT_TEST
( avarchar2(300),
bvarchar2(300)
)

二，创建prog文件：（见：XXUTS_HOST_CONCURRENT_TEST.prog）

#!/bin/ksh
#----------------------------------------------------------------------
#######################################################################
# Program Name : XXUTS_HOST_CONCURRENT_TEST.prog
# Reference : N/A
# Parameters : 1-4. System Parameters
# 5. Request Id
# 6. File Name
# ---------------------------------------------------------------------
#
# Amendment History:
# Date Version By Description
# 30-Oct-2007 1.0 Simon He Initial version
#######################################################################

LDRIN_FILE=$AU_TOP/log/XXUTS_HOST_CONCURRENT_TEST.csv

if [ ! -s ${LDRIN_FILE} ]; then
echo "指定的数据文件 ${LDRIN_FILE} 在接口目录下不存在"
exit 1
fi

LDRCTL_FILE=$AU_TOP/log/XXUTS_HOST_CONCURRENT_TEST.ctl
LDRLOG_FILE=$AU_TOP/log/XXUTS_HOST_CONCURRENT_TEST.log
LDRBAD_FILE=$AU_TOP/log/XXUTS_HOST_CONCURRENT_TEST.bad

echo "+---------------------------------------------------------------------------+"
echo "数据文件: ${LDRIN_FILE}"
echo "控制文件: ${LDRCTL_FILE}"
echo "日志文件: ${LDRLOG_FILE}"
echo "错误文件: ${LDRBAD_FILE}"

# Create the SQL*Loader control file
cat > ${LDRCTL_FILE} <<!
LOAD DATA
INFILE '${LDRIN_FILE}'
APPEND
INTO TABLE XXUTS_HOST_CONCURRENT_TEST
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
A,
B
)
!

if [ $? != 0 ]; then
echo " "
echo "生成 SQL*Loader 的控制文件失败"
exit 1
fi

# Load data into Oracle database
sqlldr userid=apps/psw control=${LDRCTL_FILE} log=${LDRLOG_FILE} bad=${LDRBAD_FILE}

if [ $? != 0 ]; then
if [ -s ${LDRLOG_FILE} ]; then
cat ${LDRLOG_FILE}
fi
echo " "
echo "SQL*Loader 载入数据失败"
exit 1
fi

# Check to see whether there is error message in the log file
LDR_RESULT1=`grep 'ORA-' ${LDRLOG_FILE}`
LDR_RESULT2=`grep 'SQL*Loader-' ${LDRLOG_FILE}`

if [ ! -z "${LDR_RESULT1}" ]; then
cat ${LDRLOG_FILE}
echo " "
echo "SQL*Loader 载入数据失败"
exit 1
fi
if [ ! -z "${LDR_RESULT2}" ]; then
cat ${LDRLOG_FILE}
echo " "
echo "SQL*Loader 载入数据失败"
exit 1
fi

echo " "
echo "SQL*Loader 载入数据成功"

rm ${LDRIN_FILE}

exit 0

[本例中，csv文件传到$AU_TOP/log路径下，见prog代码]

三，把XXUTS_HOST_CONCURRENT_TEST.prog传到$FND_TOP/bin目录

四，在$FND_TOP/bin，分别运行命令：

chmod +x XXUTS_HOST_CONCURRENT_TEST.prog
perl -pi -e 's/r$//g' XXUTS_HOST_CONCURRENT_TEST.prog

ln -s $FND_TOP/bin/fndcpesr XXUTS_HOST_CONCURRENT_TEST

五，注册报表：