Search code examples
oracleodp.net

Why does Managed ODP.Net demand SERVICE_NAME?

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.

Solution

  • 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.