Re: SQL*Loader and Double Quotes

For what I've learnt from personal experience with SQL*Loader, the best options you have are as follows:

1. do not enclose strings using double quotes, choose another character or combination of characters, for instance the 'pipe' character ( | ). Of course this is prone to further problems if your file includes the pipe character in some place, other than expected.
2. change any occurrence of the double quotes inside the strings by transforming them into something like "<quote>" or any other tag of your own choosing and replace it during the data load using the SQL REPLACE function. This is probably the more "robust" solution, albeit a bit more expensive in terms of performance.

The problem is SQL Loader does not handle as you expect the double quotes, it starts from the first occurrence and when it arrives at the second quote character interprets it as the closing character, thereafter it raises an error because it didn't find the record terminator or the field terminator, whichever comes first. As far as I know there is no workaround apart from changing in some way the input file format.

Bye,
Flavio

"Angus Tracy" <angus.tracy_at_ntlworld.com> wrote in message news:f22fbfbc.0303040211.169e67d4_at_posting.google.com...
> I'm having problems loading data containing double quotes using
> SQL*Loader.
>
> For example, in the following two lines of my data file, the second
> one is being rejected.
>
> AB3019A2,"CAMBRIDGE ROAD"
> GR7013B5,"NEXT TO THE"RED LION""
>
> I've tried a few things already, including using the REPLACE function
> as shown in the control file below, but not had much success. Does
> anyone have any suggestions ?
>
> OPTIONS

(DIRECT=FALSE,SKIP=3,READSIZE=1000000,ERRORS=1000000,BINDSIZE=1000000,ROWS=1 000,SILENT=(ALL))
> LOAD DATA
> REPLACE
> INTO TABLE RAW_ADDRESS
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
> TRAILING NULLCOLS
> (S_REF CHAR
> ,STREET CHAR "REPLACE(:STREET, CHR(34))" )
Received on Tue Mar 04 2003 - 12:44:17 CST