Unable to connect to Oracle 11g database using Oracle.ManagedDataAccess.dll instead of the Microsoft System.Data.OracleClient

Question

I am trying to migrate a .Net 4.5.2 application from using Microsoft's deprecated System.Data.OracleClient dll to using Oracle's own Oracle.ManagedDataAccess.dll (using the Nuget package v12.1.1.24160419). On the machine in question I have the Oracle client installed, and I have a tnsnames.ora file set up which the original app successfully uses to connect. I can also successfully connect with Oracle's SqlPlus and SqlDeveloper tools. However if I try to use the Oracle dll my application cannot connect.

To distill this down to the very basics I have created a very simple console application (code below) to see what is going on. If the project references the Microsoft dll (with the appropriate using statement) it will connect, but if instead I reference the Oracle.ManagedDataAccess.dll (with the appropriate using statement) it complains about a TNS error:

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

I've tried adding a SERVICE_NAME clause to the tnsnames.ora file but with no improvement.

What else do I need to do?

Test app is:

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
//using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace oracleconnect
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                InitializeDBConnection();
            }
            finally
            {
                if (_con.State==System.Data.ConnectionState.Open)
                _con.Close();
            }
        }

        static private OracleConnection _con;
        private const string connectionString = "Data Source=oracledbserver2;User ID=MATTESTNHSYS;Password=thePassword";



        private static void InitializeDBConnection()
        {
            _con = new OracleConnection();
            _con.ConnectionString = connectionString;
            _con.Open();
        }
    }
}

ORACLEDBSERVER2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = OracleDBServer2)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcdb10g) (SERVER = DEDICATED) ) ) — haughtonomous
– haughtonomous, Commented Dec 15, 2016 at 15:26

Wernfried Domscheit · Accepted Answer · 2016-12-15 15:19:27Z

1

Oracle.ManagedDataAccess.dll has a different search pattern to find your tnsnames.ora file. Unlike System.Data.OracleClient or SQL*Plus Oracle.ManagedDataAccess.dll does not read TNS_ADMIN value from Registry or Environment variable.

See Oracle Data Provider for .NET, Managed Driver Configuration

data source alias in the dataSources section under <oracle.manageddataaccess.client> section in the .NET config file (i.e. machine.config, web.config, user.config).
data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.
data source alias in the tnsnames.ora file present in the same directory as the .exe.

answered Dec 15, 2016 at 15:19

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

haughtonomous Over a year ago

So according to that, it should suffice to place a copy of my 'working' tnsnames.ora file in the application executable folder?

Wernfried Domscheit Over a year ago

Yes or make a symbolic link or define it in. NET config file

martinm · Accepted Answer · 2016-12-15 12:41:55Z

0

Data Source should be defined like this:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=type_hostname)(PORT=type_port))(CONNECT_DATA=(SERVICE_NAME=type_service_name_from_tsnames)))

And the connection string like:

private const string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=type_hostname)(PORT=type_port))(CONNECT_DATA=(SERVICE_NAME=type_service_name_from_tsnames)));User ID=MATTESTNHSYS;Password=thePassword";

answered Dec 15, 2016 at 12:41

martinm

22.1k4 gold badges45 silver badges72 bronze badges

4 Comments

haughtonomous Over a year ago

Ok thanks, I'll try that. Completely different from the MS way.

martinm Over a year ago

@NeilHaughton Good resource is also here: connectionstrings.com/oracle-data-provider-for-net-odp-net

haughtonomous Over a year ago

According to that resource, my connection string should work "with TNS". I have the client installed with TNS set up (and I have a tnsnames.ora file in place that works with the Microsoft provider). It seems that the Oracle provider is not picking that up - what extra do I need to do to ensure that it finds and reads the tnsnames.ora file?

haughtonomous Over a year ago

By the way I can connect using the Oracle provider and your suggested connection string, but that string assumes a tnsnames.ora file is not available. I want to make use of that file when creating the connection.

Collectives™ on Stack Overflow

Unable to connect to Oracle 11g database using Oracle.ManagedDataAccess.dll instead of the Microsoft System.Data.OracleClient

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related