How should I escaping quotes and line breaks for Oracle SQLLoader (sqlldr) importing?

Question

Hello.

FIRST: Yes, I know about CONCATENATE and CONTINUEIF, but I might not be smart enough to understand the documentation. I don't "get" how they solve my problem. Thank you...

I am in the position of having to create a file for importing into an Oracle DB by way of sqlldr.

The columns are required to be delimited by "~" (Don't ask.) In creating the file file, I have a few concerns about the way sqlldr behaves in respect to Quotes and Newlines.

If a simple row is to contain the column values:

One, Hello~World I "Like" you, and Three

How should I output this to the file? My first guess is

One~"Hello~World I "Like" you"~Three

I imagine it would be easy to import with FIELDS TERMINATED BY "~" OPTIONALLY ENCLOSED BY '"' specified in the CTL file. What I don't know how to handle are the quotes around the Like when I create the file. Should those be additionally escaped? How?

Additional Bonus Question: The fields may contain line-brakes. If written out "raw" it would turn into

one~Line
Break~three

Is there an option in the CTL file that I can use to "stitch" these back together? The break may occur in different columns and there may be more than one break per record, or column.

Thanks in advance!

Answer 1

After some experimentation this is what I discovered:

While using

FIELDS TERMINATED BY "~" OPTIONALLY ENCLOSED BY '"'

in the CTL file and a table structure of

key:number, msg:varchar2

The right way to handle quotes and delimiters are

1~Hello World
2~"Hello~World"
3~"Hello ""World"""
4~"Hello~""World"""

So, If the data contains the delimiter, quote the whole value and replace quotes in the value with 2 quotes.

As far as newlines go, I am going to ask that in another question.