linux 脚本 sqlldr,Sqlldr一般语法和shell脚本格式

sqlldr 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