Oracle Sqlldr optionally enclosed by(GCH)

创新互联专注为客户提供全方位的互联网综合服务，包含不限于成都网站设计、做网站、高平网络推广、重庆小程序开发、高平网络营销、高平企业策划、高平品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联为所有大学生创业者提供高平建站搭建服务，24小时服务热线：13518219792，官方网址：www.cdcxhl.com

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

本文名称：OracleSqlldroptionallyenclosedby
文章起源：http://scgulin.cn/article/piiidj.html