When extracting string data from an Oracle database, it is sometimes challenging to get certain characters to appear correctly. This is often a result of data from one character set being incorrectly stored in a different character set in the Oracle database. Regardless of the root cause, the following method can be used to force extracted string data into a specific Oracle Character Set. It uses methods in the SQL query to convert the string data first to a raw format and then back to a string format.
NOTE: This method may not resolve situations where the Oracle driver (ODBC or OLE) is specifically at fault. Some Oracle driver releases have been observed to fail to recognize unicode characters above 255 even when there is no problem with the Oracle Character Set.
SQL Query that will force extracted string data into an Oracle Character Set:
SELECT
"Column1",
"Column2",
UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW("StringColumn"), 'OracleCharacterSet') AS "StringColumn"
FROM "Schema"."Object"
Example:
SELECT
"T$ITEM",
UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW("T$SEAK"), 'WE8ISO8859P15') AS "T$SEAK"
FROM "BAAN"."TWHWMD400100"
All of Oracle's recommended Character Sets (and others) can be found in section A.4.1 here:
https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/