Parsing double quotes out of sqlldr control file replace statement

Is any sort of UNIX involved?

sed s/'"'/'\"'/g

does what you ask for in UNIX.

Despite it is in the UNIX word it is necessary use Oracle notation:
1. You should not replace double qutes, just single quotes are meaningful in oracle character strings (varchar2 etc.)
2. Single quotes in oracle are escaped not as  \' (backslash-quote) but as '' (two single quotes)
So if there are double quotes, you can get it into database without replacement. Single quotes has to be replaced using:
replace(:fieldA, '''', '''''')
 

How can I find any reference of "oracle notation" in POSIX standard?

to gheist:
It is not about POSIX but about Oracle SQLLoader control file syntax:)

I guess I didn't specify what my exact problem is.  Below is the code I had been using.  I have since changed it to:

TYPE_ADDRESS_1 "replace(:TYPE_ADDRESS_1, '"', '\"')",

and receive the error message of:


SQL*Loader-350: Syntax error at line 10.
Expecting "," or ")", found ", ".
    TYPE_ADDRESS_1 "replace(:TYPE_ADDRESS_1, '"', '\"')",


The problem I have is that I am putting this data into an SQL database, and some of the data has " (double quotes) in it.  I need to put an escape character in front of them so that they can be inserted into the database with the double quotes still there.  I'm not really trying to parse out the double quotes for the sake of oracle, but rather for SQL.

LOAD DATA
INTO TABLE TEST_TABLE
APPEND
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
    TYPE_CODE,     
    TYPE_ADDRESS_1 "replace(:TYPE_ADDRESS_1, '\"', '\\\"')",
    TYPE_ADDRESS_2 "replace(:TYPE_ADDRESS_2, '\"', '\\\"')",
    TYPE_ADDRESS_3 "replace(:TYPE_ADDRESS_3, '\"', '\\\"')",
    TYPE_ZIP_CODE,              
    TYPE_STATE_CODE,
)

Select allOpen in new window

From the post above, remove the comma from TYPE_STATE_CODE...I was only including the first few lines of my control file and forgot to delete that character.

Try to include an Expression keyword into field specifications:
TYPE_ADDRESS_1 EXPRESSION "replace(:TYPE_ADDRESS_1, '\"', '\\\"')",

Since adding the EXPRESSION keyword, I've been running into an error saying that "not all data is bound" (it's a custom error message displayed by a program that is used to load data based on the control file).

I have tried:

TYPE_ADDRESS_1 EXPRESSION "REPLACE(:TYPE_ADDRESS_1, '\"', '''')",   -- replace double quote with single quote

TYPE_ADDRESS_1 EXPRESSION "REPLACE(:TYPE_ADDRESS_1, '\"', '\"\"')",  -- replace double quote with two double quotes (first double quote as escape character in SQL)

TYPE_ADDRESS_1 EXPRESSION "REPLACE(:TYPE_ADDRESS_1, '\"', '\\\"')", -- original code to replace double quotes with \"


The control file is considered valid with all three of those statements, but it isn't formatting the data in the SQL properly.  Is there a different way I can do the REPLACE statement that will allow double quotes?

When OPTIONALLY ENCLOSED BY '"' is removed, I get the same message of "not all variables bound" shows up, but the control file is still accepted as valid.

A new note:  after removing the "EXPRESSION" statement, the errors have gone away and records without double quotes in them are being put correctly into the database.  However, the 8 records that still have double quotes are erroring out.

I have tried all three lines used above and none correct the double quote in the 8 records that I have issue with.  Removing the OPTIONALLY ENCLOSED BY from the control file results in new errors popping up, and after putting it back in the only errors are with the original 8 records containing double quotes.

awking is right, I haven't noticed the problem.
You can't use optionally enclosed by " when there are such signs within fields.
 

I have run into new issues, but I should be able to resolve them - awking was right about the enclosed by statement.