OracleSqlldroptionallyenclosedby-创新互联
Oracle Sqlldr optionally enclosed by(GCH)
In all, when you edit the control file,you would rather add theclause “optionally enclosed by” inensurance.
The following example is that not use the clause “optionallyenclosed by”.
As follows, never use the clause “optionally enclosed by”in the control file.
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"
)
Next import data
[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
Diagnose
Scan import log
……
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.
Scan bad record.
[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’.
Solve the proplem.
Modify the control file.
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许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
分享文章：OracleSqlldroptionallyenclosedby-创新互联
文章分享：http://cdiso.cn/article/dppoph.html
