I am trying to load data from a file coming from an AL32UTF8 charset source to our file server which then we have to upload to our EBS database using SQL Loader. Our EBS database is charset is set as WE8ISO8859P1 NLS_CHARACTERSET and NLS_LANGUAGE is American. I am submitting a EBS concurrent request that runs a sql loader file. When a diacritic is in the file, the record fails. It appears that a character is added when this happens. 'JOSÉ' becomes 'JOSÉ'. And no matter what we have tried, 'AMPLIACIÓN COLONIA' is converted to 'AMPLIACIÿN COLONIA'. Our DBAs say that changing the charset in either database is a not an option. To confirm this is what is causing the issue, I did edit the file and replaced the accented characters to non-accented ones. The program ran without error and loaded all of the records when there is no accents. Here is the message from the log file from the concurrent request: (The Column it is giving in the error is not the column with the accent)
Record 7: Rejected - Error on table TABLE_NAME, column COLUMN.
Invalid zoned decimal byt TABLE_NAME, column COLUMN.e.
Record 5838: Rejected - Error on tab
Invalid zoned decimal lebyte.
Table TABLE_NAME:
6273 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
So far my team has tried the following things in the SQL LOADER file without success.
"TRANSLATE(:column_name USING NCHAR_CS)" and "TRANSLATE(:column_name USING CHAR_CS)"
TRANSLATE(:column, 'ÁÀÂÄÃÅÇÉÈÊËÍÌÎÏÑÓÒÔÖÕØÚÙÛÜ', 'AAAAAACEEEEIIIINOOOOOOUUUU');
convert(:column,'WE8ISO8859P1') and convert(:column,'WE8ISO8859P1','AL32UTF8') - the program ran without error, but the data was not inserted into the database correctly. It inserted ******* *******R¿ K**** instead of ******* *******RÍA K_**_.
-REPLACE(:column, 'Ó', 'O')
utl_raw.cast_to_varchar2((nlssort(:column, 'nls_sort=binary_ai'))) - this resulted in more errors
We also tried setting NLS_LANG which resulted in program error
-NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
-NLS_LANG=AMERICAN_AMERICA.AL32UTF8
-NLS_LANG=AMERICAN_AMERICA.UTF8
and setting CHARACTERSET which didn't work
-CHARACTERSET WE8ISO8859P1
-CHARACTERSET AL32UTF8
-CHARACTERSET UTF8
We have tried the following that works in TOAD, but doesn’t work when ran through EBS:
select TRANSLATE(convert(upper('string causing error in the file'),'WE8ISO8859P1'),'ÁÀÂÄÃÅÇÉÈÊËÍÌÎÏÑÓÒÔÖÕØÚÙÛÜ', 'AAAAAACEEEEIIIINOOOOOOUUUU') from dual
select TRANSLATE(convert(upper('string causing error in the file'),'US7ASCII'),'ÁÀÂÄÃÅÇÉÈÊËÍÌÎÏÑÓÒÔÖÕØÚÙÛÜ', 'AAAAAACEEEEIIIINOOOOOOUUUU') from dual
SELECT regexp_replace(regexp_replace('string causing error in the file','[[=O=]]+','O' ),'[[=E=]]+','E' ) FROM dual;
Any help on fixing this is appreciated.