Parsing double quotes out of sqlldr control file replace statement

I have an SQLLDR control file where fieldA that is going into the DB at times contains a double quote.  I have tried to parse out the " in the following way:

...
fieldA "replace(:fieldA, '\"'', '\\\"'')",
...

This is attempting to take every instance of " in the incoming file and replace it with \" to allow the double quote to be put into the database.  This runs without error, but the quotes are still causing problems upon insert, so the replacement isn't working right.

How should I modify this statement in order to allow the double quote in the input file field to be put into the DB?
LVL 4
densvenskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
Is any sort of UNIX involved?

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

does what you ask for in UNIX.
0
JankovskyCommented:
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, '''', '''''')
 
0
gheistCommented:
How can I find any reference of "oracle notation" in POSIX standard?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JankovskyCommented:
to gheist:
It is not about POSIX but about Oracle SQLLoader control file syntax:)
0
densvenskAuthor Commented:
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,
)

Open in new window

0
densvenskAuthor Commented:
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.
0
JankovskyCommented:
Try to include an Expression keyword into field specifications:
TYPE_ADDRESS_1 EXPRESSION "replace(:TYPE_ADDRESS_1, '\"', '\\\"')",

0
densvenskAuthor Commented:
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?
0
awking00Commented:
What happens if you get rid of the OPTIONALLY ENCLOSED BY '"' in your control file?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
densvenskAuthor Commented:
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.
0
densvenskAuthor Commented:
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.
0
JankovskyCommented:
awking is right, I haven't noticed the problem.
You can't use optionally enclosed by " when there are such signs within fields.
 
0
densvenskAuthor Commented:
I have run into new issues, but I should be able to resolve them - awking was right about the enclosed by statement.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.