Solved

Parsing double quotes out of sqlldr control file replace statement

Posted on 2008-10-17
13
6,040 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

688 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