Why would Oracle.ManagedDataAccess not work when O

问题:

I'm developing a very simple application which I intend to use to troubleshoot an issue I am having on a few machines but before I even got that far I ran into a few issues, including cpu architecture differences and Oracle database libraries.

I have a database server listed in tnsnames.ora, sitting in my C:\oracle\11g\network\admin directory. If I tnsping this server I get the desired response. If I code my C# program to connect to this server with the following code using Oracle.DataAccess.Client, it works.

string connectionString = "Data Source=DSDSDS;User Id=UNUNUN;Password=PWPWPW;";
DataTable dataTable = new DataTable();

using (var connection = new OracleConnection(connectionString)) {
    connection.Open();
    using (var command = new OracleCommand()) {
        command.Connection = connection;
        command.CommandText = sql;
        command.CommandType = CommandType.Text;
        using (var oda = new OracleDataAdapter(command)) {
            oda.Fill(dataTable);
        }
    }
}

However Oracle.DataAccess is dependent on the architecture of the system it runs on. I saw that there is another library Oracle.ManagedDataAccess which is architecture independent. When I use this library it no longer is able to connect to the server. An ORA-12545: Network Transport: Unable to resolve connect hostname is thrown.

Why is this the case? What is different between these two libraries because based upon what I've read thus far this shouldn't be an issue.

Extra information:

• %ORACLE_HOME% and %TNS_ADMIN% are NOT defined (remember that tnsping and Oracle.DataAccess work)
• PATH has C:\oracle\11g\BIN defined.
• My machine only has one tnsnames.ora file

If I move tnsnames.ora to the same location as my .exe file, it works. Why can Oracle.DataAccess find tnsnames.ora in the C:\oracle\11g\network\admin directory but Oracle.ManagedAccess cannot?

回答1:

The order of precedence for resolving TNS names in ODP.NET, Managed Driver is this (see here):

1. data source alias in the 'dataSources' section under section in the .NET config file.
2. data source alias in the tnsnames.ora file at the location specified by 'TNS_ADMIN' in the .NET config file.
3. data source alias in the tnsnames.ora file present in the same directory as the .exe.
4. data source alias in the tnsnames.ora file present at %TNS_ADMIN% (where %TNS_ADMIN% is an environment variable setting).
5. data source alias in the tnsnames.ora file present at %ORACLE_HOME%\network\admin (where %ORACLE_HOME% is an environment variable setting).

I believe the reason your sample works with Oracle.DataAccess but not with Oracle.ManagedDataAccess is that Windows registry based configuration is not supported for the latter (see documentation) - the ODP.NET installation sets an ORACLE_HOME registry key (HLKM\SOFTWARE\Oracle\Key_NAME\ORACLE_HOME) which is recognized only by the unmanaged part.

回答2:

Try to add the path to tnsnames.ora to the config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.manageddataaccess.client>
    <version number="4.112.3.60">
      <settings>
        <setting name="TNS_ADMIN" value="C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

回答3:

To avoid all the Oracle mess of not knowing where it is looking for the TNSNAMES.ORA (I have the added confusion of multiple Oracle versions and 32/64 bit), you can copy the setting from your existing TNSNAMES.ORA to your own config file and use that for your connection.
Say you're happy with the 'DSDSDS' reference in TNSNAMES.ORA which maps to something like:

DSDSDS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=DSDSDSHost)(Port=4521)))(CONNECT_DATA=(SERVICE_NAME=DSDSDSService)))

You can take the text after the first '=' and use that wherever you are using 'DSDSDS' and it won't need to find TNSNAMES.ORA to know how to connect.
Now your connection string would look like this:
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=DSDSDSHost)(Port=4521)))(CONNECT_DATA=(SERVICE_NAME=DSDSDSService)));User Id=UNUNUN;Password=PWPWPW;";

回答4:

I had the similar issue......to solve this what I did was to uninstall the ODP. Net and re-install in the same directory as oracle server......with server option you will notice that most of the products are already installed (while 12c database installation) so just select the other features and finally finish the installation....

Please note that this workaround works only if you have installed 12c on the same machine i.e. on your laptop............

If your database is located on the server machine other than your laptop then please select client option and not the server and then include TNS_ADMIN in your app.config and do not forget to specify the version...

since my installation is on my laptop so my App.config is as below:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
    </startup>
</configuration>


 /////////the below code is a sample from oracle company////////////////


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;

///copy these lines in a button click event 
    string constr = "User Id=system; Password=manager; Data Source=orcl;";
// Click here and then press F9 to insert a breakpoint
        DbProviderFactory factory =
    DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = constr;
                try
                {
                    conn.Open();
                    OracleCommand cmd = (OracleCommand)factory.CreateCommand();
                    cmd.Connection = (OracleConnection)conn;

//to gain access to ROWIDs of the table
//cmd.AddRowid = true;
                    cmd.CommandText = "select * from all_users";

                    OracleDataReader reader = cmd.ExecuteReader();

                    int visFC = reader.VisibleFieldCount; //Results in 2
                    int hidFC = reader.HiddenFieldCount;  // Results in 1

                    MessageBox.Show(" Visible field count: " + visFC);

                    MessageBox.Show(" Hidden field count: " + hidFC);


                    reader.Dispose();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                      MessageBox.Show(ex.StackTrace);
                }
            }

回答5:

Once I found what format it was looking for in the connection string, it worked just fine like this with Oracle.ManagedDataAccess. Without having to mess around with anything separately.

DATA SOURCE=DSDSDS:1521/ORCL;

回答6:

I received the same error message. To resolve this I just replaced the Oracle.ManagedDataAccess assembly with the older Oracle.DataAccess assembly. This solution may not work if you require new features found in the new assembly. In my case I have many more higher priority issues then trying to configure the new Oracle assembly.