Issue to use REPLACE or TRANSLATE function in SQL*LOADER control file
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
INTO TABLE "SCHEMA"."MY_TABLE"
FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '"'
COLUMN_1 CHAR(15) "LPAD(:INVOICEDISTRIBUTIONID, 15, '0')",
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.
You are right. This may be the final solution. because this sqlldr control file contain more than 80 columns. if using position, I have to set up all columns in position and need to count so many spaces. This is why I didn't use fixed position and want to use function to remove the double quote during the loading process. Thanks for your advice.