historycrmerror.ctl
注意:表中所有integer或number类型的都要用"TO_NUMBER()"转换一下
- load data
- infile ''
- APPEND into table historycrmerror
- FIELDS TERMINATED BY ',' optionally enclosed by '"'
- TRAILING NULLCOLS
- (
- ERRORTIME CHAR(16),
- PAIORDERTYPE "TO_NUMBER(:PAIORDERTYPE)",
- SERIALNUMBER "TO_NUMBER(:SERIALNUMBER)",
- COID CHAR(20),
- CHECKFLAG "TO_NUMBER(:CHECKFLAG)",
- TIMESTAMP CHAR(16),
- SRCDEVICENO CHAR(16),
- USERID CHAR(40),
- PROVINCENO CHAR(2),
- CITYNO CHAR(3),
- AREACODE CHAR(4),
- CUSTOMERID CHAR(20),
- USERNAME CHAR(200),
- CERTIFICATETYPE CHAR(2),
- CERTIFICATENO CHAR(40),
- USERPAYTYPE CHAR(2),
- PREPAYSYSTEMNO CHAR(14),
- SERSETTYPE CHAR(2),
- BINDINGTELENO CHAR(40),
- USERIDSTATUS CHAR(2),
- PASSWORD CHAR(64),
- USERIDTYPE CHAR(2),
- UIM CHAR(40),
- CUSTOMERFLAG "to_number(:customerflag)",
- DESCRIPTION CHAR(255),
- PRINTFLAG Integer "0"
- )
historycrmerror_sqlldr.sh
- #! /bin/sh
- #export ORACLE_HOME=/home/oracle/O9Home/9.2.0
- #export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
- #需要根据具体配置修改UNIDIR,一般情况与.bash_profile中相同
- #export UNIDIR=$HOME/svn/udb/udbcore/_bin/linux
- #export PATH=$ORACLE_HOME/bin:/sbin:$PATH:$UNIDIR:.
- . $HOME/.bash_profile
- DB_USER=udb_systest
- DB_PWD=udb
- DB=udb
- CTL_PATH=$UNIDIR/job/historycrmerror/ctl
- DATA_PATH=$UNIDIR/log
- LOG_PATH=$UNIDIR/job/historycrmerror/log
- CTL_FILE='historycrmerror.ctl'
- BAD_PATH=$UNIDIR/job/historycrmerror/bad
- STOREDAY=0
- insert_db()
- {
- storedate=`date +%Y%m%d --date=$STOREDAY' days ago'`
- echo $storedate
- for datafile in `ls $DATA_PATH/UDBCRM_ERROR_*.csv`
- do
- basedataname=`basename ${datafile%.*}`
- datadate=`expr substr $basedataname 14 8`
- echo $datadate
- if [ $datadate -le $storedate ] ; then
- datename=`date +%Y%m%d%H%M%S`
- LOG_FILE=$basedataname'_'$datename.out
- info=`sqlldr userid=$DB_USER/$DB_PWD@$DB control=$CTL_PATH/$CTL_FILE data=$datafile log=$LOG_PATH/$LOG_FILE direct=true`
- mv $datafile $datafile'_'$datename.bak
- fi
- done
- }
- insert_db