4

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]
Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
  • on server A there should be tnsnames.ora which contains correct service name and connection string for the remote database on server B (ADDRESS=(protocol_address_information)) (CONNECT_DATA= (SERVICE_NAME=service_name))) Check this service name against the DDL of database link used in step 4. Trigger – Martina Dec 16 '16 at 23:35
  • Wouldn't a configuration problem on Server A be ruled out by the successful running of the same proc from an IDE? – Tom Halladay Dec 17 '16 at 18:06
  • Does the ORA-12154 occur when ODP.NET attempts to connect to Server A? Or is it part of a error stack that shows up after the Execute? Big difference. Maybe you can share the full error stack. Also, you should triple check the ODP.NET connect string. It is possible you aren't connecting to Server A at all, but to some test server or to another schema on Server A. In any case, ODP.NET should have no impact on the database link connection at all. – Christian Shay Dec 20 '16 at 01:33
  • @ChristianShay it only occurs on execution of queries referencing the linked server, not on connection nor on execution of non-linked server queries. I will try to get more stack trace today – Tom Halladay Dec 20 '16 at 16:33
  • Error stack added – Tom Halladay Dec 20 '16 at 18:00
  • Same behavior happen on your own systems if you create a simple test? – topshot Dec 22 '16 at 17:11
  • @ChristianShay is this reference / article relevant? "DBLinks are not supported in .NET stored procedures." https://docs.oracle.com/cd/E15296_01/doc.111/e15167/extenRest.htm#CJAHJBJI – Tom Halladay Dec 22 '16 at 18:25
  • @Tom Halladay: No. That doc is actually referring to use of ODP.NET inside of the database - using Oracle Database Extensions for .NET. The writer should have made this clear at the start of that section,sorry. Since you are not using Oracle Database Extensions for .NET this does not apply. – Christian Shay Jan 08 '17 at 21:41
  • @ChristianShay found the problem & solution; see accepted answer – Tom Halladay Jan 24 '17 at 18:30

3 Answers3

1

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.

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
  • From our testing, this doesn't seem to be the case. From the same client machine, using SQL Plus is successful while ODP.Net fails. I've updated the question with more detailed information – Tom Halladay Dec 19 '16 at 20:45
0

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

Antonio
  • 1,178
  • 14
  • 14
  • So we are already using TNS-less connections, we specify everything in the connection string the same way you described in #3, and this has been perfectly successful everywhere except this instance and only when querying against the linked database tables – Tom Halladay Dec 20 '16 at 16:38
  • 1
    sorry, I take back what I said. A couple questions: 1. In the end, do you know how SQLplus connects to the DB? Is a local instance or is it resolving the name somehow magical? 2. What does a `SELECT * from dba_db_links` returns? Is it possible that you are using a Global DBLink (i.e. not really a DBlink)? 3. of course, you are using the same user when connecting from sqldeveloper and odp.net. For instance the OS authentication is disabled in your odp.net client, isn't it? – Antonio Dec 21 '16 at 08:24
  • 4. send the offending query as well. I am mostly interested in the `from table@dblink` part, and how it relates to the results from `SELECT * from dba_db_links`. 5. Issue a `select user from dual` both from the odp.net and from sqldeveloper. Pretty obvious they return the same user, but we want to rule out any difference due to the grants of the user. – Antonio Dec 22 '16 at 07:50
  • Great troubleshooting directions @Antonio, waiting on the results for the DBLinks query. The SQL Plus connection was with credentials in the run command. I can confirm we were using the same accounts to run the tests. – Tom Halladay Dec 22 '16 at 17:05
  • DB Link type is PUBLIC with a Fixed User that is not the account we are executing as. Querying DBA_DB_LINKS is not permitted under our account, and USER_DB_LINKS returns nothing, so we had to run the query as an admin – Tom Halladay Dec 22 '16 at 18:13
  • :( I'm afraid this may not be a code issue, but maybe related to the driver. If you can, post the PL/SQL code; at least the relevant parts: the **whole** header of the procedure (with the `PRAGMA` directives, if any) and the part of the body that contains the SQL query that fails. – Antonio Dec 23 '16 at 09:47
  • we did find the problem & solution; see the accepted answer – Tom Halladay Jan 24 '17 at 18:31
0

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

Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
  • On the database server, was there an alias in TNSNAMES.ORA called TNS_NAME set to value you used in your second database link example? The database link is created in the database server and so it references the TNSNAMES.ORA that is local to that server. It has no access to the TNSNAMES.ORA on the client (where ODP.NET resides). – Christian Shay Jan 25 '17 at 22:33
  • @ChristianShay there must have been? Because connecting from the same client machine to the same server using SQL PLUS and running the same queries were successful from the start. I really don't think this was a question of whether the DB Link was set up "right" because it was working for a variety of other purposes, it looks very much like a bug or limitation specific to ODP.NET. – Tom Halladay Jan 26 '17 at 15:37
  • 1
    incredible, and I'm even more confused than before, but thanks for posting the answer. Clearly this feature should be completely transparent to the client (connecting to a dblinked table). It must have been a bug that bubbled up from server's implementation to client's. – Antonio Feb 22 '17 at 17:07