Exception trying to retrieve column with 'i' in the column name using ODAC in turkish

Question

I'm hitting an exception when trying to access data from an Oracle database using ODP.NET (ODAC). The code goes like this:

Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("tr-tr");
string s = "SELECT MyString from MyTable";
var conn = new OracleConnection(connectString);
conn.Open();
var cmd = new OracleCommand(s, conn);
var rdr = cmd.ExecuteReader();
rdr.Read();
object o1 = rdr[0];
object o2 = rdr["MYSTRING"];
object o3 = rdr["MyString"];

the o1 and o2 assignments work OK, but the o3 assignment gives an exception:

System.IndexOutOfRangeException: Unable to find specified column in result set
   at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
   at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)

I'm fully aware of the "turkish I" problem. It looks to me like a bug in ODAC (it's converting the column name to upper case using the current culture rather than the invariant culture) but I haven't found anyone else reporting similar problems.

two questions:

1. is this really a bug in ODAC or am I doing something wrong?

2. If it is a bug, as a workaround can I simply convert all column names to upper case (using invariant culture) when retrieving named columns?

Answer 1

I don't think this is related to any language setting. By default column names in Oracle are all upper case. If you like to have it case sensitive, try

string s = "SELECT MyString AS \"MyString\" from MyTable";

Of course, in this case object o2 = rdr["MYSTRING"]; will fail.

In case it is a problem with Turkish language:

Oracle.DataAccess does not depend on current .NET culture settings, it inherits setting from NLS_LANG value. Set NLS_LANG value properly before you start your application, for example to

NLS_LANG=TURKISH_TURKEY.AL32UTF8

You can set it either as Environment Variable or in Registry at HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32 bit application on 64 bit Windows), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 64 bit).

Another solution is to change it in Oracle session before you execute the query like this:

cmd.CommandText = "ALTER SESSION SET NLS_TERRITORY = 'TURKEY'";
cmd.ExecuteNonQuery();

Or you can use the ODP.NET Managed Driver, this is not NLS_LANG sensitive. It is only .NET locale sensitive (but does not support thread-based globalization), see Data Provider for .NET Developer's Guide