CODEWITHSUNDEEP
oracle-sqldeveloperdouble-quotescontrolfile
Elakkiya
Elakkiya

Reputation: 1

How to remove double quotes in control files (oracle sql developer)

I'm using below commands in control file

OPTIONS (SKIP=0)
LOAD DATA
BADFILE "Load_bad.bad"
TRUNCATE
INTO TABLE LOAD_DATA
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
{
  "NAME" char
  "AGE" char
  "ID" char
  "REASON" char
  "INTEREST" char 
  "EXPERIENCE" char
}

I'm passing below values for the control file but values is not loaded into oracle database due to double quotes issue. How to remove double quotes and load it into the data base. can anyone share your ideas on how to handle this scenario in control file.

myname|12|11|"need to switch" to parent|"java" c|2|

Values are not loaded into the oracle database . If I remove double quotes, then data is loaded into the table but I want the control file to handle the double quotes.

Upvotes: 0

Views: 106

Answers (2)

kowale gaurav
kowale gaurav

Reputation: 1

OPTIONS (SKIP=0)
LOAD DATA
BADFILE "Load_bad.bad"
TRUNCATE
INTO TABLE LOAD_DATA
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
{
  NAME char ,
  AGE char,
  ID char,
  REASON char "REPLACE(REPLACE(:REASON , '\"', ''), '\"', '')",
  INTEREST char "REPLACE(REPLACE(:INTEREST, '\"', ''), '\"', '')", 
  EXPERIENCE char 
}

Upvotes: 0

MT0
MT0

Reputation: 168470

Rather than changing the control file, fix the data by surrounding the values in double quotes and escaping the double quotes by doubling them:

myname|12|11|"""need to switch"" to parent"|"""java"" c"|2|

Alternatively, if you are not using double quotes around any of the values then modify the control file and remove:

OPTIONALLY ENCLOSED BY '"'

Upvotes: 0

Related Questions