I need to use sqlloader to load a few flat file into staging tables in Oracle 10g. One column in the flat file contains - " "- in the string. I want to use REPLACE or REGEXP_REPLACE or TRANSLATE function in sqlloader control file to remove " " out. The basic code are like these:

1. "translate(: column_name, 'A"', 'A')",

2. "replace(:column_name, '"', null)", or " replace(: column_name, '"')",

These functions are working for sure.

However, when I put these function into control file. It will not work with the error message like: SQL*LOADER -350 syntax error token longer than max allowable length of 258 chars for RELACE, or syntax error expecting "," or ">", found "A" for TRANSLATE.

Here is my sqlldr control file

LOAD DATA
INFILE 'C:\file_1.DEL'
BADFILE 'C:\file_1.BAD'
DISCARDFILE 'C:\file_1.dsc'
TRUNCATE
INTO TABLE "SCHEMA"."MY_TABLE"
FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COLUMN_1 CHAR(15) "LPAD(:INVOICEDISTRIBUTIONID, 15, '0')",
COLUMN_2 CHAR(15),
COLUMN_3 CHAR(150) "REPLACE(:COLUMN_3,'"')"
)

This is my flat file string. It is terminated by ^ and enclosed by " I need to take double quote around "BOARD DIRECTOR" off through data loading by sqlldr

"65129478""51386316""PROVIDE PROFESSIONAL SERVICES "BOARD DIRECTOR" AS DEVELOPED AND CUSTOMIZED THROUGH FEBRUARY 08, 1995."^

Is anyone familiar with this function or you dealed with the same sqlloader job before? Please give me some advice where is the problem in syntax. Thanks.