1

I have:

  • Win 7 (64)
  • VS 2010
  • Oracle Client 11g (32 - client_1 | 64 - client_2)
  • ODP.NET (32 - c:\oracle\ | 64 - c:\oracle64)

I have old project C# with added Oracle.DataAccess.dll (v2) in project references.

In Win PATH I have only C:\Oracle11g\product\11.2.0\client_2\bin; (64)

If I do start the application in any mode get ORA-Error

Oracle.DataAccess.Client.OracleException ORA-12154: TNS:could not resolve the connect identifier specified

In app.config

<connectionStrings>
  <add name="ConnectionStringTEST" connectionString="DATA SOURCE=TEST;PASSWORD=TEST;PERSIST SECURITY INFO=True;USER ID=TEST"
   providerName="Oracle.DataAccess.Client" />
 </connectionStrings>

How do I know what TNSNAMES library uses Oracle.DataAccess.dll in C# app?

I have in REGEDIT set all ORACLE_HOME (etc.) correctly.

I thought that using tns_names which is in the directory that is listed in the PATH system, but I found that it uses tns_names of ODP.net I install certain into c:\oracle\ (32), WHY ? ()

I do not know why it not use standard Oracle client which refers in system PATH.

On the other computers where it is installed, only one oracle client (32), compiled application runs without problems(errors).

Jan Sršeň
  • 1,045
  • 3
  • 23
  • 46
  • I recommend going through these steps: https://blogs.msdn.microsoft.com/kaevans/2009/07/18/connecting-to-oracle-from-visual-studio/ - Good luck – sjokkogutten Jan 07 '16 at 14:21
  • How did you install your Oracle components? Compare with this instruction: http://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 – Wernfried Domscheit Jan 07 '16 at 16:36
  • What is `tns_names`? I know only file `tnsnames.ora` or environment variable `TNS_ADMIN`. – Wernfried Domscheit Jan 07 '16 at 16:37

2 Answers2

1

I recommend you skip TNS names altogether. If you can tnsping the server successfully (on any machine) and get the elements you need, you can use Oracle's EzConnect string instead. This has several advantages:

  1. When you have multiple versions installed, as you mentioned, there is no confusion -- it doesn't matter because you're not using tnsnames
  2. When you deploy on another's machine, there is no dependency on their tnsnames being set up correctly
  3. If a server is migrated, you can update the code and not worry if any target machines for the app have had tnsnames updated or not

Advantage #3 is a double-edged sword, because with tnsnames you could edit the tns files and never have to deal with changing the connection string, but that's a very small price to pay in my opinion.

So, where you normally would say Data Source=TEST, if you tnsping test:

> tnsping test

and you get something like this:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = myserver.foo.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TEST)))

You could change your data source string to:

Data Source=myserver.foo.com:1521/TEST
Hambone
  • 15,600
  • 8
  • 46
  • 69
  • EzConnect is very simple to use, however it has also many limitations. See the bunch of options you have with tnsnames.ora [Local Naming Parameters](https://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm), e.g. load balancing or failover – Wernfried Domscheit Jan 07 '16 at 16:45
  • When I use `tnsping test` - I get TNSNAMES from client_2, but I can not use shortened or the whole field of TNSNAMES. My application works on several computers and HOST in tnsnames.ora can be change sometimes . Therefore, I need only use the app.config SID, but i dont know which tnsnames.ora app use and why (use ODP 32b tnsnames.ora). I'm interested in why application uses exactly the tnsnames.ora from ODP. On other computers and servers with only Oracle Client everything works well. I dont have set a TNS_NAMES in another MS Win Variable in REGEDIT – Jan Sršeň Jan 11 '16 at 11:03
  • Have you considered storing the connection data somewhere in a bootstrap process? This way, you can get the best of both worlds. I understand why tnsnames is appealing, but everything you described reinforces to me why it might be best to just connect directly. That said, if you go to the target machine and type `set` it will show you all environment variables and their settings and that might give you a clue as to why it's picking the Oracle environment – Hambone Jan 11 '16 at 13:02
0

Oracle may look for file tnsnames.ora (or more precisely the file sqlnet.ora which then defines whether tnsnames.ora shall be read or not) in these location:

  • Environment Variable TNS_ADMIN

  • Registry setting: HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN for 64-bit

    or HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN for 32-bit

  • Folder %ORACLE_HOME%\network\admin

  • The current folder

  • Folder where your application binaries are located

See also this anser: Determining locatation of relevant tnsnames.ora file

Community
  • 1
  • 1
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110