Oracle Sqlldr Optionally enclosed by

Oracle Sqlldr Optionally enclosed by (GCH)

If you edit the control file,you would rather add theclause "Optionally enclosed by" inensurance.

The following example is, and 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 hh24: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 hh24:mi:ss '), ' yyyymmddhh24:mi:ss ')",

VERSION_ID constant "3"

)

Next Import Data

[Email protected]]$ sqlldr Daidai/love8013control=mb_performance_td_cell. CTL

Sql*loader:release 11.2.0.4.0-production on Tue June 14 22:35:52 2016

Copyright (c) 1982, 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

Diagnosescan 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 is asfollows:

Therefore, the columns in the control filecorrespond to th

E Columns of goal table. However, there were still a fewrecords that were isn't imported into table.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/82/C3/wKiom1dgHXXQCNJeAABRKXgZQM4825.png-wh_500x0-wm_3 -wmp_4-s_1069748574.png "title=" 1.png "style=" FONT-SIZE:16PX; "alt=" wkiom1dghxxqcnjeaabrkxgzqm4825.png-wh_50 "/ >scan BAD record.

[Email protected]]$ 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,, Dazhou , Qu County , Huawei , Sdz6nr5 : Qu County Wangjiang Court _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-ddhh24: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 hh24:mi:ss '), ' yyyymmddhh24:mi:ss ')",

VERSION_ID constant "3"

)

Import data successfully.

Sql> Select COUNT (*) Frommb_performance_td_cell

2;

COUNT (*)

----------

50908

This article is from the "90SirDB" blog, be sure to keep this source http://90sirdb.blog.51cto.com/8713279/1789351

Oracle Sqlldr Optionally enclosed by