Solved

Parsing double quotes out of sqlldr control file replace statement

Posted on 2008-10-17
13
5,772 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 62

Expert Comment

by:gheist
ID: 22747508
Is any sort of UNIX involved?

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

does what you ask for in UNIX.
0
 
LVL 6

Expert Comment

by:Jankovsky
ID: 22749188
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 62

Expert Comment

by:gheist
ID: 22749640
How can I find any reference of "oracle notation" in POSIX standard?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Expert Comment

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

Author Comment

by:densvensk
ID: 22757510
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
ID: 22757518
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
 
LVL 6

Expert Comment

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

0
 
LVL 4

Author Comment

by:densvensk
ID: 22758083
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 32

Accepted Solution

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

Author Comment

by:densvensk
ID: 22758257
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
ID: 22758546
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
ID: 22758717
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
ID: 31507306
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

840 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