Solved

Parsing double quotes out of sqlldr control file replace statement

Posted on 2008-10-17
13
5,518 Views
Last Modified: 2013-12-07
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?
0
Comment
Question by:densvensk
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 61

Expert Comment

by:gheist
Comment Utility
Is any sort of UNIX involved?

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

does what you ask for in UNIX.
0
 
LVL 6

Expert Comment

by:Jankovsky
Comment Utility
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
 
LVL 61

Expert Comment

by:gheist
Comment Utility
How can I find any reference of "oracle notation" in POSIX standard?
0
 
LVL 6

Expert Comment

by:Jankovsky
Comment Utility
to gheist:
It is not about POSIX but about Oracle SQLLoader control file syntax:)
0
 
LVL 4

Author Comment

by:densvensk
Comment Utility
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
 
LVL 4

Author Comment

by:densvensk
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

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

0
 
LVL 4

Author Comment

by:densvensk
Comment Utility
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
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
Comment Utility
What happens if you get rid of the OPTIONALLY ENCLOSED BY '"' in your control file?
0
 
LVL 4

Author Comment

by:densvensk
Comment Utility
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
 
LVL 4

Author Comment

by:densvensk
Comment Utility
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
 
LVL 6

Expert Comment

by:Jankovsky
Comment Utility
awking is right, I haven't noticed the problem.
You can't use optionally enclosed by " when there are such signs within fields.
 
0
 
LVL 4

Author Closing Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now