Connecting to an Oracle server you know is there. But connection still fails. What gives.

Connecting to an Oracle server you know is there. But connection still fails. What gives.

  • Comments 2

For some reason I have lately got a few cases related to connecting to Oracle.

All seems to be well in terms of Oracle being up and running etc, but still the client can't connect.

 

To add to the confusion, there are different errors depending on how you (try) to connect to the Oracle server.

 

I'll show you three ways of connecting, and their respective error messages. So, for the sake of showing the different errors, we have 3 clients.

 

Client 1 - A .Net application with the System.Data.OracleClient classes:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

        static void Main(string[] args)

        {

            string cStr = @"Data Source=<server>;User Id=<user>;Password=<password>";

            using (OracleConnection oc = new OracleConnection(cStr))

            {

                try

                {

                    oc.Open();

                    oc.Close();

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

            }

        }

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Client 2 - An old school vbscript file (*.vbs) client:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

            WScript.Echo "Connecting..."

            Dim con

            set con = CreateObject("ADODB.Connection")

            con.Open "Provider=MSDAORA;Data Source=<server>;User ID=<user>;Password=<password>"

            WScript.Echo "Connected, now closing"

            con.Close

            WScript.Echo "Done!"

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Client 3 - A plain UDL.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

  - Create a .txt on the desktop.

  - Rename it to Test.udl

  - Click it and fill in the details like so:

    Provider : Microsoft OLE DB Provider for Oracle

    Server  : <your server>

    User Name            : <your username>

    Password : <your password>

  - Now click "Test Connection"

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

The reason for having 3 clients is, as mentioned, that they all, annoyingly enough, shows somewhat different errors even thought the problem is the same.

So, these are the scenarios and the errors, for both scenarios you are trying to connect to your Oracle server, but receive the following errors:

 

Scenario 1:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Client 1 gives:

System.Exception: OCIEnvCreate 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.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()

 

Client 2 gives:

Microsoft OLE DB Provider for Oracle: Oracle error occurred, but error message could not be retrieved from Oracle.

 

Client 3 gives:

Test connection failed because of an error in initializing provider. Oracle error occurred, but error message could not be retrieved from Oracle.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Scenario 2:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Client 1:

System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

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

 

Client 2:

Microsoft OLE DB Provider for Oracle: Oracle client and networking components were not found.

These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

 

Client 3:

Test connection failed because of an error in initializing provider. Oracle client and networking components were not found.

These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

(note that in scenario 2., I only have Oracle 10 installed, and still there are references to Oracle versions that have never been close to this machine. Confusing).

 

The solution/reason?

 

For the first scenario the most common reason is that your ORACLE_HOME setting in the registry is incorrect.

Depending on the Oracle installation, the whereabouts may differ, but typically it is found here:

  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME

 

The value for this key is usually something like (this where I installed it, you may have installed it elsewhere):

  C:\oraclexe\app\oracle\product\10.2.0\server

Make sure that this is pointing to the correct directory for your Oracle installation. In the tests I did for this blog, I simply changed the value in registry to:

  C:\Program Files\oraclexe\app\oracle\product\10.2.0\server

and I got the errors in scenario 1., changing it back to the correct value resolves this.

 

For the second scenario the most common reason is that your %PATH% variable is incorrect.

Check this by right clicking on ‘My Computer’ -> Properties -> Advanced -> Environment Variables -> System Variables-> Path.

Make sure that this contains the correct location for you Oracle directories, again, changing:

 C:\oraclexe\app\oracle\product\10.2.0\server

 to

  C:\Program Files\oraclexe\app\oracle\product\10.2.0\server

will produce the error in scenario 2.

 

So, if getting the errors above, make sure that ORACLE_HOME and %PATH% variables are correct.

Another reason for this is that is related to the above is that you are logged on as a user that may not have the correct access rights on either on the Oracle directories

or on the Oracle keys in the registry. Or that Oracle is installed in one place by one user, for example the administrator,

and then installed again by a different user at a different location. This may mess up the ORACLE_HOME and %PATH& settings.

 

Finally, make sure that you really have the Oracle client software installed in the first place J

 

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
Page 1 of 1 (2 items)