Set NLS_LANG for oracle managed data access

Question

I have a winforms c# application using Oracle 12c through oracle.manageddataaccess (via NHibernate 4.0), The oracle database is on another machine on customer site we've had issues with character set conversion due to NLS_LANG differences between the oracle database and the client machine. There is no Oracle client installed on the client machine (just using Managed driver).

We've found that you can specify the client NLS_LANG using an environment variable.

My question is: Are there any other ways to specify the NLS_LANG setting when using the new Managed Data Access in Oracle?

Answer 1

You might want to check out the Windows Registry for NLS_LANG keys. Setting the key is more or less equivalent to setting the environment variable, but if both exists the environment variable takes precedence. This StackOverflow question were a little bit related to this question :

Effects of changing NLS_LANG setting in the registry for Oracle Client

During my installation session today, we found out that our Web Application is running in 32-bit mode over the 64-bit OS, so the registry location being used is different from the standard one. It is in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1. I would suggest to do a global search using regedit to find out where are the NLS_LANG keys are.

Answer 2

I am using NHibernate, but you could probably run this with whatever you are using.

ALTER session SET nls_language = 'AMERICAN'

I execute it right after opening a database connection.

Once that is done, running this returns 'US'.

select USERENV('LANG') from dual;

And you can run this to get a list of possible values for nls_language.

select * from V$NLS_VALID_VALUES where parameter = 'LANGUAGE'

Answer 3

No, this is not possible, see documentation Data Provider for .NET Developer's Guide where it says

ODP.NET, Managed Driver is not NLS_LANG sensitive. It is only .NET locale sensitive.

Also see OracleGlobalization Properties

• ClientCharacterSet -> public string ClientCharacterSet { get; }

  Specifies a client character set. Not Available in ODP.NET, Managed Driver

Answer 4

The problem raises when you migrates from unmanaged to managed ODP. Here are method for setting NLS_LANG by it's parts to opened OracleConnection

        string nlsLang = Settings.Default.NLS_LANG;
        var arr = nlsLang.Split('_');
        string language = arr[0];
        arr = arr[1].Split('.');
        string territory = arr[0];
        string characterSet = arr[1];

        OracleGlobalization info = connection.GetSessionInfo();
        info.Language = language;
        info.Territory = territory;
        info.NumericCharacters = characterSet;
        connection.SetSessionInfo(info);

Answer 5

I am using the OracleGlobalization to set Date format as follows. This might give you a clue..

 conn = new OracleConnection(connectionString);
        conn.Open();
        OracleGlobalization info = conn.GetSessionInfo();
        info.DateFormat = "YYYY-MM-DD";
        conn.SetSessionInfo(info);