sqlldr导入

historycrmerror.ctl

注意：表中所有integer或number类型的都要用"TO_NUMBER()"转换一下

load datainfile ''APPEND into table historycrmerrorFIELDS 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_profileDB_USER=udb_systestDB_PWD=udbDB=udbCTL_PATH=$UNIDIR/job/historycrmerror/ctlDATA_PATH=$UNIDIR/logLOG_PATH=$UNIDIR/job/historycrmerror/logCTL_FILE='historycrmerror.ctl'BAD_PATH=$UNIDIR/job/historycrmerror/badSTOREDAY=0insert_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