2

I have an ODBC datasource to an Oracle database. The table I select data from has varchar2 columns containing e.g "kg/m³" or "°C".

Now using an ODBCConnection in my c# application, the OdbcDataReader returns "kg/m? and "?C".

Checking the NLS parameters with:

SELECT * 
FROM V$NLS_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

returns AL32UTF8 for NLS_CHARACTERSET and AL16UTF16 for NLS_NCHAR_CHARACTERSET.

If I change the column type to NVARCHAR, the OdbcDataReader returns the right value. But changing the table columns is not an option because the database is also used by other applications.

Is there any way to get the right value by setting any parameter on the connection, command or reader objects?

Thanks, Cew3

Edit: Setting the system environment NLS_LANG to GERMAN_GERMANY.AL32UTF8 (and rebooting the machine!) solved the problem!

Thank you all for the hints/comments!

TallTed
  • 9,069
  • 2
  • 22
  • 37
cew3
  • 73
  • 6
  • Which ODBC driver do you use? – Wernfried Domscheit Dec 14 '18 at 13:53
  • 1
    This might be your local NLS_LANG environment variable on your client machine - the V$NLS_PARAMETERS view shows the database's charset, which can be different from your client's. https://stackoverflow.com/questions/4955363/oracle-odbc-why-are-national-characters-changed-to-latin-equivalent-in-select-r – kfinity Dec 14 '18 at 13:54
  • 1
    @kfinity, indeed it can be different (without any problem!). In case you like to make them equal - what would you set? `AL32UTF8` or `AL16UTF16`? Imagine you select a `VARCHAR2` and a `NVARCHAR2` data type column in one SELECT statement. See https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600 – Wernfried Domscheit Dec 14 '18 at 14:00
  • Nice post! I just meant that OP's NLS_LANG might be incorrectly set - I don't do much C# coding, but if it uses UTF-16 by default and `NLS_LANG=.WE8ISO8859P1`, wouldn't that cause this kind of data corruption? – kfinity Dec 14 '18 at 14:08
  • Are you sure about the ODBC driver? Please post your ConnectionString. Also post the exact `NLS_LANG` value. What do you get from `DUMP(, 1016)`? – Wernfried Domscheit Dec 14 '18 at 14:34
  • Again, are you sure you use the Oracle ODBC driver? I never read that it takes the `NLS_LANG` into account. Perhaps you use the ODBC driver from [Progress DataDirect](https://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/Unicode_Support_8.html) which utilize `NLS_LANG` value. – Wernfried Domscheit Dec 14 '18 at 15:35

2 Answers2

0

Most likely you are using the ancient and deprecated Microsoft ODBC for Oracle driver from Microsoft. It does not support UTF-8.

The ODBC Driver for Oracle does not support any of the new Oracle8 data types - Unicode data types, BLOBs, CLOBs, and so on - nor does it support Oracle's new Relational Object Model.

Use the ODBC driver from Oracle (e.g. ODAC Runtime Downloads), this one should work.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

Solution was:

Setting the system environment NLS_LANG to GERMAN_GERMANY.AL32UTF8 and rebooting the machine!

TallTed
  • 9,069
  • 2
  • 22
  • 37