使用sqlldr装载数据时碰到如下问题:
所有字段使用空格分隔,其中有两个字段使用双引号包含(其中可能有空格),数据样本、域分隔如下:
60.23.202.124 26.120.3.61 - - 1 [09/Oct/2011:09:00:01 +0800] "POST /BocnetClient/PAYSNo&SignDynPayments.do HTTP/1.1" 200 23089 "https://ebs.boc.cn/BocnetClient/PAYSNoSignDynPayments.do?&_ts=1318121995190&AcctId=34711856&PayeeId=0&PrvcShortName=LN&CityDispName=%E8%90%A5%E5%8F%A3&AcctRealNum=6013820500484210525&MerchantId=02041109&Date=2011%2F10%2F09&AcctDispNo=6013***********0525&UserCode=7801280&PayeeDispName=%E7%BD%91%E9%80%9A%E8%AF%9D%E8%B4%B9%E7%BC%B4%E7%BA%B3"
------------- ----------- - - - ---------------------------- ------------------------------------------------------- --- ----- --------------------------------
看起来很复杂,其实通过使用OPTIONALLY ENCLOSED BY就可以轻松实现。
另外,由于该字段较长,需要在控制文件中说明字段长度(超过255),否则报错。
--1,创建临时表
drop table tmp_webaccess;
create table tmp_webaccess
(
col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20),
col5 varchar2(20),
col6 date,
col8 varchar2(200),
col9 varchar2(100),
col10 varchar2(100),
col11 varchar2(200)
);
--2,创建控制文件
more webaccess.ctl
LOAD DATA
infile "webaccess.txt"
Append INTO TABLE tmp_webaccess
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
col1,
col2,
col3,
col4,
col5,
col6 "to_date(replace(:col6,'['),'dd/mm/yyyy:hh24:mi:ss')",
col7 filler,
col8 char(4000),
col9,
col10,
col11 char(4000)
)
--3,输入数据文件
more webaccess.txt
60.23.202.124 26.120.3.61 - - 1 [09/Oct/2011:09:00:01 +0800] "POST /BocnetClient/PAYSNo&SignDynPayments.do HTTP/1.1" 200 23089 "https://ebs.boc.cn/BocnetClient/PAYSNoSignDynPayments.do?&_ts=1318121995190&AcctId=34711856&PayeeId=0&PrvcShortName=LN&CityDispName=%E8%90%A5%E5%8F%A3&AcctRealNum=6013820500484210525&MerchantId=02041109&Date=2011%2F10%2F09&AcctDispNo=6013***********0525&UserCode=7801280&PayeeDispName=%E7%BD%91%E9%80%9A%E8%AF%9D%E8%B4%B9%E7%BC%B4%E7%BA%B3"
--4,装载
sqlldr user/pwd control=webaccess.ctl
--另:sqlldr支持远程加载数据,甚至支持easyconnect.
easyconnect条件:
(1),安装Oracle客户端
(2),客户端$ORACLE_HOME/network/admin/sqlnet.ora中需要支持EZCONNECT:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
否则报:
[oracle]$ sqlldr mh/mh@22.11.97.96:1521/ora10 control=fund_inf.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Apr 29 09:59:08 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified
(3),连接方式:
sqlplus mh/mh@//22.11.97.96:1521/ora10
sqlldr mh/mh@22.11.97.96:1521/ora10 control=fund_inf.ctl
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-709034/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-709034/