0

I am migrating a .Net 4.6.2 application from using Microsoft's System.Data.OracleClient to Oracle's own managed ODP.Net (Nuget package, version 12.2.11)for connecting to an Oracle database. I have the Oracle client installed (11gR2) and apart from changing references, using clauses, type names etc the code is pretty much the same. However, whilst my pre-migrate code connects quite happily, the post-migrate will not connect. Instead it throws an OracleException 

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA.

The tnsnames.ora file is the same, and the application's connection string is the same, and I have never before needed to specify a service name.

TnsNames.ora:

ORACLEDBSERVER2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = OracleDBServer2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = orcdb10g)
      (SERVER = DEDICATED)
    )

Connection string:

USER ID=MATTESTNHADMIN;PASSWORD=XXXXX;DATA SOURCE=oracledbserver2

Why is it now demanding a SERVICE NAME? Even if I add a clause to the CONNECT_DATA part (SERVICE_NAME = orcdb10g) I get the same error.

haughtonomous
  • 4,602
  • 11
  • 34
  • 52

1 Answers1

1

I assume your program does not find the tnsnames.ora file. Unlike the old drivers the ODP.NET managed driver does not read TNS_ADMIN value from the Registy.

See this answer https://stackoverflow.com/a/28283924/3027266 to see how ODP.NET managed driver is looking for tnsnames.ora file.

Note, when you use ODP.NET managed driver then you don't have to install any further Oracle client.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks for that insight. Copying the tnsnames.ora file to the executable folder seems to have done the trick. I'd love to know why Oracle have introduced this seemingly pointless change! To avoid confusion I guess a symlink is called for here. – haughtonomous Nov 16 '17 at 08:40
  • As I noted in linked answer there is no common way how your `tnsnames.ora` file is discovered. There are several options but all should point to the same folder. Yes, if required by using symbolic links. – Wernfried Domscheit Nov 16 '17 at 09:45