Effects of changing NLS_LANG setting in the registry for Oracle Client

We are in the process of moving from the .NET Microsoft oracle driver to the ODP.NET driver.

One of the problems we have had is this error:

ORA-12705: Cannot access NLS data files or invalid environment specified

We were able to stop the error by modifying the registry and changing the setting (see this question)

In our case we changed

HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE - NLS_LANG

which was set to NA

to be the same as

HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE - HOME0 - NLS_LANG

which was set correctly

My question is why would there be different NLS_LANG settings in the registry, and might there be any knock on effects of changing this value?

Update: I've just found in the Oracle 开发者_高级运维NLS FAQ the following

For Oracle version 7:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

For Oracle Database versions 8, 8i and 9i:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx\

where "x" is the unique number identifying the Oracle home.

HOME0 is the first installation

For Oracle Database 10g:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name>

There you have an entry with name NLS_LANG

OK, so there are different registry settings for different versions...

Note:

Some people are confused by finding a NLS_LANG set to "NA" in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE when no version 7 was installed. This is used for backwards compatibility, and can be ignored.

I have Oracle 9i, so now I'm even more confused - why is the ODP.NET dll looking at the Oracle 7 registry setting?

I had a similar problem with the;

ORA-12705: Cannot access NLS data files or invalid environment specified

The ODP.NET dll's or instant client were reading the registry;

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG=NA

The value NA caused the error.

This was because I already had a client installation but I wanted to use the oracle instant client via network drive for the a VB.NET app with ODP.NET.

My simple fix in my vb.net solution was for example to adjust the environment for the application via:

Environment.SetEnvironmentVariable("NLS_LANG", 
                    "AMERICAN_AMERICA.WE8MSWIN1252", 
                    EnvironmentVariableTarget.Process)

nb. The Oracle "NLS FAQ" link is no longer valid (2012)

PER Oracle Notes on the 11g ODP release, the following can cause this error:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG=NA <--- This NA does in fact cause this error if set to NA.

You can try DELETING the key if not needed or setting it to a valid NLS_LANG setting for your locale.

For us we set it to AMERICAN_AMERICA.WE8MSWIN1252.

In our case we did not want to make any potentially breaking changes to the Oracle registry because we were installing our web service on a production Oracle 9i server.

The solution was simply to prevent ODAC from being able to see any ORACLE registry keys by denying all access to that key for the user ID our web service was running as.

1. Start -> Run... regedit (as an administrator)
2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
3. Right click on the ORACLE key -> Permissions...
4. Click the Add... button.
5. Add the web service user name configured in your web service's application pool identity (e.g. IUSR_MyWebService); this is the user name that appears against your w3wp.exe process in Task Manager.
6. Press OK.
7. For the new user permissions, check "Deny" against the Full Control permission and press OK.

This worked just fine and as a bonus we have ensured that our application is isolated from any future changes to the ORACLE registry keys.

Tip: you can prove to yourself that the user in question has no access to the keys in question by closing any running instances of the Registry Editor, start a CMD prompt as that user (using Run As...) and then launching regedit from the command prompt.

the Oracle Client (ORACLE_HOME\bin\ora*.dll) is looking for a file named "oracle.key" in the same directory. This file contains the name of the registry key which belongs to this Oracle client installation. (e.g. "Software\ORACLE\HOME3") hth Andreas

This was all resolved in the end by installing the ODAC 11 client components (downloaded from the Oracle website). I think the system was getting confused because we had copied the ODAC dlls across rather than fully installing the client. ODP.NET is expecting an Oracle 11 client and didn't know where to find the Oracle Home.

NB if you are installing the ODAC components using xCopy deployment then do not install them to an existing Oracle Home directory (eg c:\oracle\ora92 for 9i client). This causes a 'Provider is not compatible with the version of Oracle Client’ error.