sqlldr导入

最新推荐文章于 2022-05-18 08:58:21 发布

原创最新推荐文章于 2022-05-18 08:58:21 发布 · 762 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#path #integer #insert #file #oracle #date

ORACLE 专栏收录该内容

7 篇文章

订阅专栏

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