ITPub博客

首页 > 数据库 > Oracle > sqlldr例子

sqlldr例子

原创 Oracle 作者:410192979 时间:2016-03-22 22:09:09 0 删除 编辑
导入核心数据:
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: sqlnet.ora跟踪设置
请登录后发表评论 登录
全部评论

注册时间:2014-03-08

  • 博文量
    151
  • 访问量
    108867