7

I'm getting the following error while trying to connect to an Oracle database from a new ASP.NET MVC 4 application: "ORA-12154: TNS:could not resolve the connect identifier specified". I'm using the Oracle.ManagedDataAccess DLL (version 4.121.1.0) to try to connect to an Oracle 10g database. Here's the thing - I have an integration test assembly that is successfully connecting to the database using this minimal App.config:

  <connectionStrings>
    <add name="OracleConnection" connectionString="DATA SOURCE=TNS_NAME;PASSWORD=xxx;PERSIST SECURITY INFO=True;USER ID=xxx" providerName="Oracle.ManagedDataAccess.Client" />
  </connectionStrings>

However, if I try to run my web app with all the crazy Web.config settings, I'm getting the error "ORA-12154: TNS:could not resolve the connect identifier specified". What am I doing wrong? Why is my config for the integration test assembly so simple and the Web.config so complex? Here's the pertinent sections of my Web.config (taken from Deploying and Configuring ODP.NET to work without installation with Entity Framework):

custom configSection:

<configSections>
    <section name="oracle.manageddataaccess.client"
        type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  </configSections>

the corresponding config section:

  <oracle.manageddataaccess.client>
    <version number="*">
      <edmMappings>
        <edmMapping dataType="number">
          <add name="bool" precision="1"/>
          <add name="byte" precision="2" />
          <add name="int16" precision="5" />
        </edmMapping>
      </edmMappings>
    </version>
  </oracle.manageddataaccess.client>

custom system.data node:

  <system.data>
    <DbProviderFactories>
       Remove in case this is already defined in machine.config 
      <remove invariant="Oracle.DataAccess.Client" />
      <remove invariant="Oracle.ManagedDataAccess.Client" />
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client"
             description="Oracle Data Provider for .NET, Managed Driver"
             type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </DbProviderFactories>
  </system.data>

EntityFramework node:

  <entityFramework>
    <defaultConnectionFactory type="Victoria.Data.OracleConnectionFactory, EntityFramework" />
  </entityFramework>

Update 1: After reading through http://docs.oracle.com/cd/E16655_01/win.121/e17732/featConfig.htm#ODPNT8161, I tried modifying my Web.config oracle.manageddataaccess.client to the following and it works. However, it doesn't seem right to have the connectionString node referencing the TNS name AND this extra reference to the same TNS Names file.

  <oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="SIEBMATS" descriptor="(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.yyy.zzz)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = siebmats)))" />
      </dataSources>
      <edmMappings>
        <edmMapping dataType="number">
          <add name="bool" precision="1"/>
          <add name="byte" precision="2" />
          <add name="int16" precision="5" />
        </edmMapping>
      </edmMappings>
    </version>
  </oracle.manageddataaccess.client>
Community
  • 1
  • 1
Andy
  • 2,541
  • 4
  • 29
  • 60
  • Are you in a 64bit environment? – Pow-Ian May 05 '14 at 15:02
  • Yes, I'm running Windows 7 64 bit. I've set my assemblies to build using "Any CPU". – Andy May 05 '14 at 15:09
  • I have had probnlems in the past where I was debugging and testing and everything was working fine because VS is a 32 bit application and thus was calling the 32bit oracle client. I Had to install the 64bit oracle client in order to run the application. You may be having a similar issue. I would check to be sure you have a 64 bit oracle client installed on your server. – Pow-Ian May 05 '14 at 15:13

2 Answers2

8

One thing you can try is having the connection string like so: connectionString="Data Source=//SERVER:PORT/INSTANCE_NAME;USER=XXX;PASSWORD=XXX".

Useful references here: http://www.connectionstrings.com/oracle/ http://docs.oracle.com/cd/E51173_01/win.122/e17732/featConnecting.htm#ODPNT169

This has also helped me while having problems with EF: Deploying and Configuring ODP.NET to work without installation with Entity Framework

Hope to have helped.

Community
  • 1
  • 1
Anarud
  • 116
  • 3
0

My problem was in incorrect/incomplete Oracle Driver version, had 11.2.0 installed. I add another 12.2.0 version, didn't change anything in web.config and it worked as charm

Piotr Grudzień
  • 155
  • 1
  • 8