oracle - Sqlldr- No terminator found after terminated and enclosed field -


i use oracle 11g.
data file looks below:

1|"\a\ab\"|"do not "clean" needles"|"@"

2|"\b\bg\"|"wall "69" side end"|"@"

my control file is:

load data infile 'short.txt' continueif last <> '"' table "portal"."us_full"  fields terminated "|" optionally enclosed '"' trailing nullcols (   u_hlevel,   u_fullname nullif u_fullname=blanks,   u_name char(2000)  nullif c_name=blanks ,   u_no nullif u_no=blanks ) 

while loading data through sqlldr, .bad file created , .log file contains error message stating "no terminator found after terminated , enclosed field"

double quotes starting , ending not in data, need double quotes withing data in above example surrounding clean , 69. ex: data file after loading should like:

1, \a\ab\, not "clean" needles, @

2, \b\bg\ , wall "69" side end , @

how accomplish this?

i'm afraid since fields surrounded double-quotes double-quotes want preserve need escaped adding double-quote in front this:

1|"\a\ab\"|"do not ""clean"" needles"|"@" 

alternately if can data without fields being surrounded double-quotes, work too:

1|\a\ab\|do not "clean" needles|@ 

if can't data provider format data needed (i.e. search double-quotes , replace 2 double-quotes before extracting file), have pre-process file set double quotes 1 of these ways data load expect.


Comments