189 8069 5689
Oracle Sqlldr optionally enclosed by(GCH)
In all, when you edit the control file,you would rather add theclause “optionally enclosed by” inensurance.
OPTIONS(skip=1)
LOADDATA
INFILE"/home/oracle/sqlldr/MB_PERFORMANCE_TD_CELL.csv"
APPENDINTO TABLE MB_PERFORMANCE_TD_CELL
Fieldsterminated by ","
trailingnullcols
(EVENT_DAYtimestamp "yyyy-mm-dd hh34:mi:ss",
HOUR_P,
CITY_NAME,
COUNTRY_NAME,
VENDOR,
CELL_NAME,
CGI,
RLC_THRPUT ,
UTRAN1_NUM,
UTRAN2_NUM ,
RECORD_TIME"to_timestamp(to_char(sysdate,'yyyymmdd hh34:mi:ss'),'yyyymmddhh34:mi:ss')",
VERSION_ID constant "3"
)
[oracle@daidaisqlldr]$ sqlldr daidai/love8013control=MB_PERFORMANCE_TD_CELL.CTL
SQL*Loader:Release 11.2.0.4.0 - Production on Tue Jun 14 22:35:52 2016
Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commitpoint reached - logical record count 64
Commitpoint reached - logical record count 128
Commitpoint reached - logical record count 192
Commitpoint reached - logical record count 256
Commitpoint reached - logical record count 320
Commitpoint reached - logical record count 384
Commitpoint reached - logical record count 448
Commitpoint reached - logical record count 512
Commitpoint reached - logical record count 576
Commitpoint reached - logical record count 640
Commitpoint reached - logical record count 704
Commitpoint reached - logical record count 768
Commitpoint reached - logical record count 832
Commitpoint reached - logical record count 896
Commitpoint reached - logical record count 960
Commitpoint reached - logical record count 1024
……
Record96: Rejected - Error on table MB_PERFORMANCE_TD_CELL, column UTRAN2_NUM.
ORA-01722:invalid number
Record119: Rejected - Error on table MB_PERFORMANCE_TD_CELL, column UTRAN2_NUM.
ORA-01722:invalid number
Record126: Rejected - Error on table MB_PERFORMANCE_TD_CELL, column UTRAN2_NUM.
ORA-01722:invalid number
…….
From the import log, it is the conclusionthat some were imported successfully. I select from database. Samples are asfollows:
Therefore, the columns in the control filecorrespond to th
e columns of goal table. However, there were still a fewrecords that were not imported into table.
[oracle@daidaisqlldr]$ cat MB_PERFORMANCE_TD_CELL.bad
2016-06-0700:00:00,,达州,渠县,华为,SDZ6NR5：渠县涌兴_2,460-00-61844-12098,,,
2016-06-0700:00:00,,内江,内江归属未知,华为,SNJ6NR2DX：田家十中_1,460-00-61811-34907,,,
2016-06-0700:00:00,,资阳,资阳归属未知,华为,SZY6NR3：乐至大佛二站_1,460-00-61889-40577,,,
2016-06-0700:00:00,,资阳,资阳归属未知,华为,SZY4NR3：乐至仲良中学_1,460-00-61889-35687,,,
2016-06-0700:00:00,,内江,内江归属未知,华为,SNJ5NR5WY：城北_3,460-00-61812-36239,,,
2016-06-0700:00:00,,达州,宣汉县,华为,SDZ6NR3：宣汉普光105_1,460-00-61841-24707,,,
2016-06-0700:00:00,,达州,渠县,华为,SDZ6NR5：渠县望江庭_1,460-00-61844-60867,,,
Then, I think of keyword ‘optionallyenclosed by’.
cat MB_PERFORMANCE_TD_CELL.CTL
OPTIONS(skip=1)
LOAD DATA
INFILE"/home/oracle/sqlldr/MB_PERFORMANCE_TD_CELL.csv"
APPEND INTO TABLE MB_PERFORMANCE_TD_CELL
Fields terminated by "," optionlly enclosed by " "
trailing nullcols
(EVENT_DAY timestamp "yyyy-mm-ddhh34:mi:ss",
HOUR_P,
CITY_NAME,
COUNTRY_NAME,
VENDOR,
CELL_NAME,
CGI,
RLC_THRPUT ,
UTRAN1_NUM,
UTRAN2_NUM ,
RECORD_TIME"to_timestamp(to_char(sysdate,'yyyymmdd hh34:mi:ss'),'yyyymmddhh34:mi:ss')",
VERSION_ID constant "3"
)
Import data successfully.
SQL> select count(*) fromMB_PERFORMANCE_TD_CELL
2 ;
COUNT(*)
----------
50908
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。