Oracle SQL loader: Convert foreign chars to English

Question

I'm using SQL loader to load a data file into an Oracle table. The database has a character set of US7ASCII. Some of the records in the data file contain European special characters such as é or ü or î. When they are loaded into the table, the results are weird with 2 strange icons (like black diamond with an arrow inside).

Is there anyway to get SQL loader to load the nearest English equivalent instead? So the é would be loaded as e, the ü would be loaded as u and the î would be loaded as i?

They are probably not in SQL is Unicode. As for converting to "English" a program would probably be best to use a program. They are call diacritic, — paparazzo
Do you have string CHARACTERSET WE8MSWIN1252 in control file? — Egor Skriptunoff
Frisbee - I am unsure of the charset of the incoming file. I have asked the supplier to confirm this. — infaddict
Egor - I don't have any CHARACTERSET in my control file at the moment. I am now running with some tests of different CHARACTERSET values to see what difference this makes. Thanks! — infaddict

TenG TenG · Accepted Answer · 2015-08-16T10:27:25

First establish what is actually being stored. Use the Oracle SQL DUMP function, e.g. SELECT dump ( colname, 16 ) from table.

This will return the HEX code for the data in colname.

If the hex code matches the hex code (od -h) of the input then the data is stored correctly, and the issue you have is translation on display. See my answer below for how to deal with this:

when insert persian character in oracle db i see the question mark

The more likely issue you have is your database character US7ASCII cannot cope with extended character sets.

Oracle SQL loader: Convert foreign chars to English

1 Answers