Sqlldr一般语法和shell脚本格式

sqlldr dw/password@dw control=D:depms_e
mer_ctl.txt log=F:depms_emer_log.txt bad=F:dep
ms_emer_BAD.txt rows=1000

LOAD DATA
INFILE 'F:test.dat'
INTO TABLE dw.DEPMS_EMER
TRUNCATE
fields terminated by '|$|' OPTIONALLY ENCLOSED BY '"'
(
ID ,
CTL_ID ,
NUMBER_ ,
NAME ,
SCOPE1 ,
SCOPE2 ,
AREA1 ,
AREA2 ,
TYPE ,
AIM ,
REL ,
GRADE1 ,
GRADE2 ,
GRADE3 ,
GRADE4 ,
ORGAN ,
RLDATE TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF',
EFCT ,
SUMM ,
OWNER ,
GROUP_ID ,
MADE_PSN ,
VERSION ,
CASE_BAK
)

APPEND 原先的表有数据 就加在后面
INSERT 装载空表 如果原先的表有数据 sqlloader会停止 默认值
REPLACE 原先的表有数据 原先的数据会全部删除
TRUNCATE 指定的内容和replace的相同 会用truncate语句删除现存数据

时间戳 TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
2008-10-21 12:10:41.251000

[@more@]

#SQLLDR用SHELL去导入数据:

. $HOME/.bash_profile

#用户名,密码
UserId=cognos
PassWd=yuxinetl
Dbserv=`expr @cognos`
#数据文件路径
DATAPATH=`expr /datafile`
#SQL*LOADER
TAB_NAME=`expr d_zz`
IN_FILE=`expr ${DATAPATH}``expr /ZZ.tsv`
CTRL_FILE=`expr ${DATAPATH}``expr /zz.ctl`
LOG_FILE=`expr ${DATAPATH}``expr /zz.log`
BAD_FILE=`expr ${DATAPATH}``expr /zz.bad`

#清除临时文件
if [ -f ${CTRL_FILE} ]
then
rm -f ${CTRL_FILE}
else
echo ${CTRL_FILE}不存在
fi
if [ -f ${LOG_FILE} ]
then
rm -f ${LOG_FILE}
else
echo ${LOG_FILE}不存在
fi
if [ -f ${BAD_FILE} ]
then
rm -f ${BAD_FILE}
else
echo ${BAD_FILE}不存在
fi

#生成控制文件
make_ctrl()
{
printf "LOAD DATAn" >>${CTRL_FILE};
printf "INFILE '${IN_FILE}'n" >>${CTRL_FILE};
printf "INTO TABLE ${TAB_NAME}n" >>${CTRL_FILE};
printf "TRUNCATEn" >>${CTRL_FILE};
printf "FIELDS TERMINATED BY 't'n" >>${CTRL_FILE};
printf "(n" >>${CTRL_FILE};
printf " ZZRQ "to_date(:ZZRQ,'yyyy-mm-dd')",n" >>${CTRL_FILE};
printf " JGH "replace(trim(:JGH ),chr(13),'')",n" >>${CTRL_FILE};
printf " HBF "replace(trim(:HBF ),chr(13),'')",n" >>${CTRL_FILE};
printf " KMH "replace(trim(:KMH ),chr(13),'')",n" >>${CTRL_FILE};
printf " SNDJFYE "replace(trim(:SNDJFYE),chr(13),'')",n" >>${CTRL_FILE};
printf " SNDDFYE "replace(trim(:SNDDFYE),chr(13),'')",n" >>${CTRL_FILE};
printf " BNJFFSE "replace(trim(:BNJFFSE),chr(13),'')",n" >>${CTRL_FILE};
printf " BNDFFSE "replace(trim(:BNDFFSE),chr(13),'')",n" >>${CTRL_FILE};
printf " BNKHS "replace(trim(:BNKHS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BNXHS "replace(trim(:BNXHS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BNJFBS "replace(trim(:BNJFBS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BNDFBS "replace(trim(:BNDFBS ),chr(13),'')",n" >>${CTRL_FILE};
printf " SYDJFYE "replace(trim(:SYDJFYE),chr(13),'')",n" >>${CTRL_FILE};
printf " SYDDFYE "replace(trim(:SYDDFYE),chr(13),'')",n" >>${CTRL_FILE};
printf " BYJFFSE "replace(trim(:BYJFFSE),chr(13),'')",n" >>${CTRL_FILE};
printf " BYDFFSE "replace(trim(:BYDFFSE),chr(13),'')",n" >>${CTRL_FILE};
printf " BYKHS "replace(trim(:BYKHS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BYXHS "replace(trim(:BYXHS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BYJFBS "replace(trim(:BYJFBS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BYDFBS "replace(trim(:BYDFBS ),chr(13),'')",n" >>${CTRL_FILE};
printf " SRDJFYE "replace(trim(:SRDJFYE),chr(13),'')",n" >>${CTRL_FILE};
printf " SRDDFYE "replace(trim(:SRDDFYE),chr(13),'')",n" >>${CTRL_FILE};
printf " BRJFFSE "replace(trim(:BRJFFSE),chr(13),'')",n" >>${CTRL_FILE};
printf " BRDFFSE "replace(trim(:BRDFFSE),chr(13),'')",n" >>${CTRL_FILE};
printf " BRJFYE "replace(trim(:BRJFYE ),chr(13),'')",n" >>${CTRL_FILE};
printf " BRDFYE "replace(trim(:BRDFYE ),chr(13),'')",n" >>${CTRL_FILE};
printf " BRKHS "replace(trim(:BRKHS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BRXHS "replace(trim(:BRXHS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BRJFBS "replace(trim(:BRJFBS ),chr(13),'')",n" >>${CTRL_FILE};
printf " BRDFBS "replace(trim(:BRDFBS ),chr(13),'')",n" >>${CTRL_FILE};
printf " HS "replace(trim(:HS ),chr(13),'')" n" >>${CTRL_FILE};
printf ")n" >>${CTRL_FILE};
}

#判断文件是否已经下载
cd ${DATAPATH}
if [ -f ${IN_FILE} ]
then
make_ctrl
sqlldr $UserId/$PassWd${Dbserv} control=$CTRL_FILE log=$LOG_FILE bad=$BAD_FILE rows=100000
else
echo ${IN_FILE}不存在
fi
exit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23984584/viewspace-1033968/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23984584/viewspace-1033968/