Why would Oracle ManagedDataAccess not work when Oracle DataAccess does

Question

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

User · Answer

Try to add the path to tnsnames ora to the config file    lt  xml version  1 0  encoding  utf-8    gt   lt configuration gt     lt oracle manageddataaccess client gt       lt version number  4 112 3 60  gt         lt settings gt           lt setting name  TNS ADMIN  value  C  oracle product 10 2 0 client 1 NETWORK ADMIN     gt         lt  settings gt       lt  version gt     lt  oracle manageddataaccess client gt   lt  configuration gt

User · Answer

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    lt  xml version  1 0  encoding  utf-8   gt   lt configuration gt       lt startup gt            lt supportedRuntime version  v4 0  sku   NETFramework Version v4 0   gt       lt  startup gt   lt  configuration gt              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

User · Answer

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

User · Answer

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

User · Answer

The order of precedence for resolving TNS names in ODP NET  Managed Driver is this  see here     data source alias in the  dataSources  section under  section in the  NET config file  data source alias in the tnsnames ora file at the location specified by  TNS ADMIN  in the  NET config file  data source alias in the tnsnames ora file present in the same directory as the  exe  data source alias in the tnsnames ora file present at  TNS ADMIN   where  TNS ADMIN  is an environment variable setting   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

User · Answer

A  little  late to the party but the real answer to this - if you use Oracle ManagedDataAccess ODP NET provider  you should forget about things like network admin  Oracle client  Oracle Home  etc   Here is what you need   Download and install Oracle Developer Tools for VS or ODAC  Note - Dev tools will install ODAC for you  This will create relatively small installation under C  Program Files  x86   With full dev tools  under 60Mb In your project you will install Nuget package with corresponding version of ODP net  Oracle ManagedDataAccess dll  which you will reference At this point you have 2 options to connect    a  In the connection string set datasource in the following format  DataSource ServerName Port SID       or DataSource IP Port SID       b  Create tnsnames ora file  only it is going to be different from previous experiences   Have entry in it       AAA    DESCRIPTION              ADDRESS    PROTOCOL   TCP  HOST   ServerNameOrIP  PORT   1521             CONNECT DATA    SERVER   DEDICATED   SERVICE NAME   SIDNAME      And place this file into your bin folder  where your application is running from  Now you can connect using your connection name - DataSource AAA       So  even though you have tnsnames ora  with ODP net managed it works a bit different - you create local TNS file  And now  it is easy to manage it      To summarize - with managed  no need for heavy Oracle Client  Oracle home or knowing depths of oracle installation folders  Everything can be done within your  net application structures

User · Answer

In my case everything said above was OK  but I still have been receiving ORA-12545  Network Transport  Unable to resolve connect hostname  I tried to ping the Oracle machine and found out I cannot see it and added it to the hosts file  Then I received another error message ORA-12541  TNS no listener  After investigation I realized that pinging the same hostname from different machines getting different IP addresses I don t know why  and I changed the IP address in my host file  which resolved the problem on 100    I m bothering to write my experience as it seems obvious  but although I was sure the problem is in the above settings I totally forgot to check if I really can see the remote DB machine out there  Keep it in mind when you are out of ideas what is going on       These links helped me a lot   http   www moreajays com 2013 03 ora-12545-connect-failed-because-target html http   www orafaq com wiki ORA-12541

User · Answer

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

[c#] Why would Oracle.ManagedDataAccess not work when Oracle.DataAccess does?

Examples related to c#

Examples related to oracle

Examples related to tnsnames