Execute 32bit C# console application which connects to Oracle 9.2 on Windows 2003 R2 Server

I have problems launching a small C# console application to run on Windows 2003 R2 Server. The demo is used to check why the connection to Oracle 9.2 that is situated on the Windows 2003 R2 Server can't be established running the same code from WCF hosted on IIS.

    static void Main(string[] args)
    {
        try
        {

            string CONNECTION_STRING =
                      "User Id=YYYY;Password=ZZZZ;Data Source=(DESCRIPTION=" +
                      "(ADDRESS=(PROTOCOL=TCP)(HOST=X.XXX.X.X)(PORT=1521))" +
                      "(CONNECT_DATA=(SID=ORCL)));";
            string oradb = CONNECTION_STRING;

            using (OracleConnection conn = new OracleConnection(oradb))
            {
                conn.Open();
                Console.WriteLine("Connected to Oracle Database {0}", conn.ServerVersion);
                Console.WriteLine();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from cur_clients";
                cmd.CommandType = CommandType.Text;

                OracleDataReader dr = cmd.ExecuteReader();
                dr.Read();

                //var Text = dr.GetString(0);
                object[] values = new object[dr.FieldCount];
                dr.GetValues(values);
                foreach (var value in values)
                {
                    Console.Write("{0},",value);
                }

                conn.Close();
                //conn.Dispose();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error : {0}", ex);
        }
        Console.WriteLine("Press ENTER to continue");
        Console.ReadLine();
    }

}

The server has installed the Oracle 9.2 database. Running the same code from outside from a remote machine everything works but in local system does not.

It returns an error

[2014.08.05 17:23:37.79808] OCIEnvNlsCreate failed with return code -1 but error message text was not available.
   at System.Data.OracleClient.OciHandle..ctor(OciHandle parentHandle, HTYPE handleType, MODE ocimode, HANDLEFLAG handleflags)
   at System.Data.OracleClient.OciNlsEnvironmentHandle..ctor(MODE environmentMode)
   at System.Data.OracleClient.OCI.DetermineClientVersion()
   at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)
   at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
   at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OracleClient.OracleConnection.Open()

According to some information it is a problem of permissions to the Oracle installation folder. After giving full control to C:\Oracle\Ora92 the first problem disappeared but the next came out.

    Error : System.Data.OracleClient.OracleException (0x80131938): ORA-12705: invalid or unknown NLS parameter value specified

   at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integ
   at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)
   at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPo
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptio
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OracleClient.OracleConnection.Open()
   at OracleConsoleTest.Program.Main(String[] args)

After searching the registry for the NLS, language parameter I found that on location HKLM\SOFTWARE\ORACLE\ there was a parameter NLS_LANG with value "NA"

Removing this my application started but all other 3rd party Oracle applications stopped working.

I really do not know what is the right approach on how to connect to the old Oracle 9.2 version.

I tried the newest .NET drivers from Oracle but they do not support Oracle 9.2

One more information: SQLPlus from the same server works perfectly. The database can be access without problems.

Any help regarding this problem is appreciated.

I resolved the problems using Instant Client from Oracle at

http://www.oracle.com/technetwork/topics/winsoft-085727.html.

It was required to copy the dlls in the same folder as the executable.

In this way I resolved OCIEnvNlsCreate exception. The NLS_LANG exception I resolved setting the environment variable from the code itself. I was not allowed to change the registry settings so I had to set the variable from my code and it worked. In this way the environment variable is set only for the current running process.

Environment.SetEnvironmentVariable("NLS_LANG", "American_America.UTF8");