导入核心数据:
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/