Managed ODP.NET Calls to PUBLIC Database Link tables cause TNS errors

Question

Our application uses managed ODP.NET code to call a variety of Oracle Procedures.

For one of our clients, who is using public database links, and referencing the linked tables within the procedure, that call is failing. Upon further testing, any attempt to run a query via ODP.NET to the linked table fails.

ORA-12154: TNS: could not resolve the connect identifier specified

Full Stack:

Oracle.ManagedDataAccess.Client.OracleException: ORA-12154: TNS:could not resolve the connect identifier specified 
at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) 
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF) 
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at [APPLICATION CODE]

This is strange because attempts to use SQL PLUS to make the same queries succeed, even though there is no TNS Names ora file on the client machine.

Our app does not even use TNS-Names, it uses explicit connection string details.

We've even run test queries through the DevArt oracle driver and they are also successful.

It's as though specifically making the calls via Managed ODP.NET makes the server treat the Database link differently.

ODP.NET  Query (Client Machine A) > (Server A) > Table (Server B) = ERROR
SQL Plus Query (Client Machine A) > (Server A) > Table (Server B) = SUCCESS
DEV ART  Query (Client Machine A) > (Server A) > Table (Server B) = SUCCESS

The test query we're using now is a simple SELECT statement

Does anyone know how we can make ODP.NET defer to the server for this information the same way SQL Plus does?

DB Link Information:

OWNER:    PUBLIC
USERNAME: [FIXED OTHER USER]

Answer 1

Our client was able to fix this problem on the server side. The issue was the format of the DB Link.

Original DB Link:

CREATE PUBLIC DATABASE LINK [LINK_NAME] 
CONNECT TO [USER] 
IDENTIFIED BY [PASSWORD] 
USING [TNS_NAME]

Apparently over ODP.NET, the TNS Names Reference is insufficient. Once the client switched to a full connection detail, the issue was resolved.

Updated DB Link:

CREATE PUBLIC DATABASE LINK [LINK_NAME] 
CONNECT TO [USER] 
IDENTIFIED BY [PASSWORD]
USING
'(DESCRIPTION = 
     (SDU=[SDU])
     (ADDRESS_LIST = 
          (ADDRESS = (PROTOCOL = TCP)(HOST = [HOSTNAME])(PORT = [PORT]))
     (CONNECT_DATA = (SID=[SID])
)'

More details here:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

Answer 2

This is surely a problem with the connection (database link) between Server A and Server B.

It's possible that the TNS name for server B is missing in the tnsnames.ora file for server A, or maybe server A has the wrong TNS name in its database link.

If this was a client-side issue I can imagine receiving an ORA-12154 error, but not ORA-04088: clearly your connection to server A is okay if you can get into the trigger.

I would say that the next step would be to connect directly to the database on Server A and try querying a table in the database on Server B over the database link. I would expect this to fail with the same ORA-12154 error.

Answer 3

From your description, chances are that:

1. it is a configuration problem of your ODP.NET client.
2. furthermore, the client may not be connecting to Server A in the first place, much less to Server B.

The fact that ODP.NET is a thin client (no native code involved) should rule out any configuration related to the Oracle Client binaries: the driver is "self contained" and totally independent from your SQLplus installation. What I mean is that for instance, if there is an error in the %path% variable of your machine, it is affecting the configuration of data, not the binaries involved.

Potentially, ODP.NET may not see a proper ORACLE_HOME variable (e.g. C:\u01\app\client\product\12.1.0\client_1\). And possibly, the configuration error manifests in ODP.NET's inability to get the location of your TNS names file.

I would suggest to:

1. try to see if there is another client that appears first in your path, and hides your intended directory. For instance: C:\u01\app\client\product\11.2.1\client_1\bin;C:\u01\app\client\product\12.1.0\client_1\bin. In such case, as a quick try, put your ODP.NET client first. This may well make your ODP.NET code pick up the pointer to the right Oracle_HOME, and the TNS configuration would follow. For complex setups, consider that Oracle stores the diverse ORACLE_HOME values in the registry and provides you with ways others than the position in %path% to select the one you want. See Using Multiple Oracle Homes if interested. Of course do not forget to put your TNS names in the right place, and this TNS should point at Server A, not Server B.
2. alternatively try to create an additional environment variable visible to the Windows account which is executing the ODP.NET code; this variable should be called TNS_ADMIN and point to the admin directory of your Oracle client (something like ..product\12.1.0\client_1\network\admin). Make sure your ODP.NET sees it by issueing string tns_admin = Environment.GetEnvironmentVariable("TNS_ADMIN") from the ODP.NET client.
3. If the above doesn't work, try to avoid the TNS stuff altogether, specifying all the info right in the connection string like this:

<connectionStrings> <add name="Server_A" connectionString="SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername;pwd=myPassword;" /> </connectionStrings>

The latter setup is shown in here or the slightly different alternative here.

I think that embedding the TNS info in your ODP.NET configuration is actually cleaner than any other solution which relies on the host OS configuration, because makes your ODP.NET truly portable (deploying to TEST or PRODUCTION is matter of changing the ODP.NET configuration, not the host variables). For more examples on how to relate TNS and ODP.NET configuration see Oracle Managed ODP.NET | Vijay's blog.

Finally, it doesn't surprise a lot that SQLPlus is working, because it may be seeing different environment variables to grab the connection identifiers or the ORACLE_HOME.
The fact that there is no tnsnames.ora file at all might indicate that

• either sqlpus is run specifying the connection on the command line, like sqlplus user/password@(description=(address_list=(address=.......ODS))) (see Ask Tom "How to connect SQLPlus without tnsnames.ora")
• or that the instance of Oracle resides on the same server, so doesn't strictly need a listener and you can skip the TNS configuration (see Connections That Require the Listener)