sqlldr例子

导入核心数据：

sqlload.sh：

sqlldr userid=dpfm30/dpfm30  control=/oradata/zfht/dpfm30/control.ctl  data=/oradata/zfht/dpfm30/$1 log=/oradata/zfht/dpfm30/log/$1_log.log   bad=/oradata/zfht/dpfm30/log/$1_bad.log  discard=/oradata/zfht/dpfm30/log/$1_discard.log  direct=false skip_index_maintenance=true

control:

load data

into table dpfm30_2014  append

fields terminated by '|'   optionally enclosed by '"'

TRAILING NULLCOLS  ---对于添加像entire这样的列非常有用

(DM30ACCT    

,DM30ACCS    

,DM30TXDT   date "DD-MM-YY"

,DM30PTXSQ   

,DM30CTXSQ   

,DM30CCY     

,DM30CLIPRO  

,DM30AMT     

,DM30CRDFLG  

,DM30BALE    

,DM30BALD    

,DM30ATUNTT  

,DM30OPNODE  

,DM30CTFLG   

,DM30DOCTYP  

,DM30VOUN    

,DM30PAGNO   

,DM30ITEM    

,DM30PRTFLG  

,DM30PTXCD   

,DM30TELID   

,DM30CHKTEL  

,DM30TOACCT  

,DM30TOACCS  

,DM30TXRMKC  

,DM30TXRMKS  

,DM30TXCOM   

,DM30PRTNUM  

,DM30CRDNO    )

导入时报错：

ORA-01858: a non-numeric character was found where a numeric was expected

处理方法：

需要设置：

export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

UTF8数据导入到16GBK库中

t_realtran.sh：

sqlldr userid=mpsuser/mpsuser  control=/oradata/zfht/wangk/t_realtran.ctl  data=/oradata/zfht/wangk/$1 log=/oradata/zfht/wangk/log/$1_log.log   bad=/oradata/zfht/wangk/log/$1_bad.log  discard=/oradata/zfht/wangk/log/$1_discard.log  direct=true skip_index_maintenance=true

t_realtran.ctl：

load data

CHARACTERSET UTF8

into table mpsuser.T_REALTRAN  append

fields terminated by '|'  OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

  (

  tranid      ,

  trantypid   ,

  trandat     ,

  hostdat     ,

  mifdat      ,

  stldat      ,

  sndorgno    ,

  sndbrhno    ,

  sndbrhnm    ,

  sndmbrno    ,

  rcvorgno    ,

  rcvbrhno    ,

  rcvbrhnm    ,

  rcvmbrno    ,

  hostagt     ,

  seqno       ,

  orseqno     ,

  mifseqno    ,

  hostseqno   ,

  fronseqno   ,

  clkseqno    ,

  frzno       ,

  cur         ,

  tranamt     ,

  ctflg       ,

  chrg1       ,

  feectflg    ,

  chrgflg     ,

  chrg2       ,

  pyracc      ,

  pyrnm       ,

  pyeacc      ,

  pyenm       ,

  certtyp     ,

  certno      ,

  cbflg       ,

  billno      ,

  remark      ,

  oprst       ,

  affiflg     ,

  dcflg       ,

  srflg       ,

  chkflg      ,

  prcco       ,

  resnco      ,

  hosterrco   ,

  nccerrco    ,

  prttimes    ,

  sndtim      ,

  rcvtim      ,

  clk         ,

  chkclk      ,

  authorgno   ,

  authclk     ,

  chnlid      ,

  msgid       ,

  refmsgid    ,

  mifdblockflg,

  reservers1  ,

  reservers2 

  )

导入前需要设置：

export LANG=zh.CN.UTF-8

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.UTF8"

卸数：

#!/bin/ksh

. /home/oracle/.profile

export ORACLE_SID=orcl

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.UTF8"

DB_USER=CNAPSDB                                #DB USER

DB_PWD=CNAPSDB                                 #DB PASSWORD

sqlplus -s $DB_USER/$DB_PWD<<EOF

set trimspool on

set linesize 1200

set pagesize 0

set newpage 0

set heading off

set term off

SET WRAP OFF

set termout off

set feedback off  

spool CNAPSDB_BUPMTRANJNL.txt

select

workdate              ||'|'||      

workseqid             ||'|'||      

worktime              ||'|'||      

templatecode          ||'|'||      

tradecode             ||'|'||      

sysid                 ||'|'||      

appid                 ||'|'||      

resid                 ||'|'||      

busidate              ||'|'||      

busiflag              ||'|'||      

chnlcode              ||'|'||      

chnldate              ||'|'||      

chnlseqno             ||'|'||      

brno                  ||'|'||      

tellerno              ||'|'||      

chktellerno           ||'|'||      

authtellerno          ||'|'||      

bankno                ||'|'||      

terminalno            ||'|'||      

mbflag                ||'|'||      

dcflag                ||'|'||      

cashflag              ||'|'||      

curcode               ||'|'||      

amt                   ||'|'||      

realtradeamt          ||'|'||      

feeflag               ||'|'||      

feemode               ||'|'||      

feetrancode           ||'|'||      

feecode               ||'|'||      

feebookcode           ||'|'||      

feeamt                ||'|'||      

feetranamt            ||'|'||      

feebookamt            ||'|'||      

msgtype               ||'|'||      

msgid                 ||'|'||      

sendbank              ||'|'||      

sendbankname          ||'|'||      

sendclearbank         ||'|'||      

sendclearbankname     ||'|'||      

recvbank              ||'|'||      

recvbankname          ||'|'||      

recvclearbank         ||'|'||      

recvclearbankname     ||'|'||      

payerbank             ||'|'||      

payerbankname         ||'|'||      

payerclearbank        ||'|'||      

payerclearbankname    ||'|'||      

payeebank             ||'|'||      

payeebankname         ||'|'||      

payeeclearbank        ||'|'||      

payeeclearbankname    ||'|'||      

payeraccbank          ||'|'||      

payeraccbankname      ||'|'||      

payeraccno            ||'|'||      

payername             ||'|'||      

payeraddr             ||'|'||      

payeeaccbank          ||'|'||      

payeeaccbankname      ||'|'||      

payeeaccno            ||'|'||      

payeename             ||'|'||      

payeeaddr             ||'|'||      

vouchtype             ||'|'||      

vouchdate             ||'|'||      

vouchno               ||'|'||      

idtype                ||'|'||      

idno                  ||'|'||      

accbookno             ||'|'||      

suspclearseqno        ||'|'||      

bankdate              ||'|'||      

bankseqno             ||'|'||      

bankrspdate           ||'|'||      

bankrspseqno          ||'|'||      

corpstatus            ||'|'||      

corperrcode           ||'|'||      

corperrmsg            ||'|'||      

corpchkflag           ||'|'||      

bankchkflag           ||'|'||      

tradebusistep         ||'|'||      

busistatus            ||'|'||      

priority              ||'|'||      

addinfo               ||'|'||      

remark                ||'|'||      

bookname              ||'|'||      

busitype              ||'|'||      

busikind              ||'|'||      

extbusitype           ||'|'||      

extbusikind           ||'|'||      

origmsgtype           ||'|'||      

origmsgid             ||'|'||      

origdetailno          ||'|'||      

origbusidate          ||'|'||      

origworkdate          ||'|'||      

origworkseqid         ||'|'||      

printcnt              ||'|'||      

batcnt                ||'|'||      

batno                 ||'|'||      

batseqno              ||'|'||      

detailseqid           ||'|'||      

detailno              ||'|'||      

detailflag            ||'|'||      

nettingnode           ||'|'||      

nettingdate           ||'|'||      

nettinground          ||'|'||      

nettingamt            ||'|'||      

cleardate             ||'|'||      

clearround            ||'|'||      

protocolno            ||'|'||      

respperiod            ||'|'||      

respdate              ||'|'||      

respstatus            ||'|'||      

rejectcode            ||'|'||      

rejectreason          ||'|'||      

revflag               ||'|'||      

cashprojectcode       ||'|'||      

errctlflag            ||'|'||      

commsgid              ||'|'||      

rspmsgid              ||'|'||      

rspmsgtype            ||'|'||      

bspno                 ||'|'||      

operbrno              ||'|'||      

accbrno               ||'|'||      

dealflag              ||'|'||      

remitflag             ||'|'||      

hasbook               ||'|'||      

sysflag               ||'|'||      

pkgdate               ||'|'||      

reserved1             ||'|'||      

reserved2             ||'|'||      

reserved3             ||'|'||      

reserved4             ||'|'||      

reserved5             ||'|'||      

chkmsg                ||'|'|| ''   

from  CNAPSDB.BUPMTRANJNL   ;      

spool off;

exit;

EOF

  

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

转载于:http://blog.itpub.net/26366371/viewspace-2062119/