Unable to insert data into ORACLE database because of NLS_LANG exception.

I have made a small app in .NET 2.0 which is copying data from a SQL Server to ORACLE.
It is running as a Windows service on the ORACLE server.
For accessing ORACLE I am using ODP.NET 11g, and for accessing SQLServer I'm using built in functionality i the .NET framework.

When running the service on the development machine, it works just great.
When running it on the ORACLE server, it fails with an exception:

"Oracle.DataAccess.Client.OracleException ORA-12705: Cannot access NLS data files or invalid environment specified    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
   at Oracle.DataAccess.Client.OracleConnection.Open()"

The same thing happens when trying to run the service on a different machine which already has ORACLE client installed.
The work-around on this PC was to actually remove the NLS_LANG entry from the reigstry.
I don't see that as the solution on the ORACLE server.

Anyone who know if this is possible?
BTW I also tried using Microsoft's ORACLE provider with the same result.
This is actually becoming a showstopper for us!
Who is Participating?
Ole_BrunAuthor Commented:
We have now tried a few things, and finally found a solution.

In the registry there are two entries for NLS_LANG.
HKLM\SOFTWARE\ORACLE was set to NA, whereas

By changing \HKLM\SOFTWARE\ORACLE to AMERICAN_AMERICA.WE8MSWIN1252 the application started working! :-)

Thanks for your suggestions though!
SujithData ArchitectCommented:
Are you able to to do an sqlplus on the server and connect to oracle?
Ole_BrunAuthor Commented:
Yes, sqlplus works ok.
It also appears that acessing the database using an old .NET 1.1 application works.
However my .NET 2.0 application is constantly getting the error message.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SujithData ArchitectCommented:
Where is it getting the NLS_LANG setting from?

Can you check the environment variables?
Is it bundled in your application itself?
Ole_BrunAuthor Commented:
It looks as if it is getting it from the reigstry.
As I mentioned, one way of solving the problem was to actually remove the registry entry.
This is not the solution on the ORACLE server.
It seems the value of NLS_LANG is wrong.
NLS_LANG on the server has no big meaning for everyday operation.
The main governing value is the character set of the database and it is
stored in the DB parameters.
NLS_LANG plays role for utilities like Export, Import.
Also check how many homes you have and check if you use the correct one.
SujithData ArchitectCommented:
Do you need the NLS_LANG setting in the server for any other applications?

If not; try to create an environment variable with name NLS_LANG and set it to an empty string and then launch the application.(The idea is to unset NLS_LANG and run your application).
Ole_BrunAuthor Commented:
The ORACLE database is part of the Honeywell Uniformance installation, and I believe the NLS_LANG setting is required by that application. However I can see that the NLS_LANG string has been configured several places in the registry. I have a colleague experimenting with removing the ones that we know are not required by the Uniformance application.
Will update you on the progress.
SujithData ArchitectCommented:
>> When running it on the ORACLE server

Do you know the userid which runs this service? You may create an env variable for this user as I suggested above and try.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.