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

Posted on 2008-11-03
Last Modified: 2013-12-17
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!
Question by:Ole_Brun
    LVL 27

    Expert Comment

    Are you able to to do an sqlplus on the server and connect to oracle?

    Author Comment

    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.
    LVL 27

    Expert Comment

    Where is it getting the NLS_LANG setting from?

    Can you check the environment variables?
    Is it bundled in your application itself?

    Author Comment

    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.
    LVL 47

    Expert Comment

    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.
    LVL 27

    Expert Comment

    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).

    Author Comment

    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.
    LVL 27

    Expert Comment

    >> 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.

    Accepted Solution

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now