9 Replies Latest reply: Mar 29, 2014 1:57 AM by Alex.Keh .Product.Manager-Oracle RSS

    Get TNSNames path programatically (ODP.NET not Managed) in C#

    kiquenet

      Hi,

       

      I use ODP.NET

       

       

      64-bit ODAC 11.2 Release 3 (11.2.0.2.1) Xcopy for Windows x64

      ODAC 11.2 Release 3 (11.2.0.2.1)

       

      (not Managed, too older and new versions installed in several PCs)

       

      64-bit ODAC 12c Release 2 (12.1.0.1.2) for Windows x64

      ODAC 12c Release 2 (12.1.0.1.2) and Oracle Developer Tools for Visual Studio

       

       

      For Oracle.DataAccess (not managed ODP.NET),


      how can I get programatically (C#) the TNSNames path the Oracle.DataAccess assembly (OracleConnection class) that I used ?


       

         [TestMethod]

              public void Version_OracleDataAccess_ODPnet_test()

              {

                  var t = typeof(Oracle.DataAccess.Client.OracleConnection);

                  TestContext.WriteLine("Assembly Oracle.DataAccess: " + t.Assembly.FullName);

                  TestContext.WriteLine("Oracle.DataAccess. Location:  " + (t.Assembly).Location);

                  TestContext.WriteLine("Oracle.DataAccess. GlobalAssemblyCache: " + (t.Assembly).GlobalAssemblyCache);

                  TestContext.WriteLine("Oracle.DataAccess. AssemblyQualifiedName: " + t.AssemblyQualifiedName);

                  TestContext.WriteLine("Oracle.DataAccess. Module.FullyQualifiedName:  " + (t.Module.FullyQualifiedName));

                  TestContext.WriteLine("\r\n");

       

       

                  Assert.IsTrue((t.Assembly).GlobalAssemblyCache, t.Name + " no está en GAC");

       

       

                  var conectado = false;

       

       

                  try

                  {

       

       

                      string connstring = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ZZZ.ZZZ.ZZZ.ZZZ)(PORT=1523))" +

            "(CONNECT_DATA=(SID=oas1d)));User Id=XXXX;Password=XXXX;";

                      using (var conn = new OracleConnection(connstring))

                      {

                          conn.Open();

       

       

                          TestContext.WriteLine("Oracle.DataAccess.OracleConnection State: " + conn.State);

                          TestContext.WriteLine("Oracle.DataAccess.OracleConnection ServerVersion: " + conn.ServerVersion);

                          TestContext.WriteLine("Oracle.DataAccess.OracleConnection DataSource: " + conn.DataSource);

       

       

                          // For ODP.NET older versions not exists property ServiceName

                          //                   

                          // System.MissingMethodException: Método no encontrado: 'System.String Oracle.DataAccess.Client.OracleConnection.get_ServiceName()'.

       

       

                          if (true)

                          {

                              //TestContext.WriteLine("Oracle.DataAccess.OracleConnection ModuleName: " + conn.ModuleName);

                              TestContext.WriteLine("Oracle.DataAccess.OracleConnection ServiceName: " + conn.ServiceName);

                              TestContext.WriteLine("Oracle.DataAccess.OracleConnection InstanceName: " + conn.InstanceName);

                              TestContext.WriteLine("Oracle.DataAccess.OracleConnection HostName: " + conn.HostName);

                              TestContext.WriteLine("Oracle.DataAccess.OracleConnection DatabaseName: " + conn.DatabaseName);

                              TestContext.WriteLine("Oracle.DataAccess.OracleConnection DatabaseDomainName: " + conn.DatabaseDomainName);

                          }

       

       

                          Assert.AreEqual(conn.State, ConnectionState.Open, "La conexión no está Open");

       

                          conectado = true;

                          conn.Close();

                      }

       

       

                      Assert.IsTrue(conectado, "Error en la conexión " + connstring);

                  }

                  catch (Exception ex)

                  {

                      Assert.Fail("Error en la conexión. Msg: " + ex.ToString());

                  }

              }

       

      thanks

        • 1. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
          Alex.Keh .Product.Manager-Oracle

          How you get your TNS path depends on where you store your tnsnames information. These are the possible paths.

           

          In your app, it appears you are placing the TNS entry into the Data Source attribute directly.

          • 2. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
            kiquenet

            I think that default path for TNSNames is ..Network\Admin

             

            Maybe programatically using HKLM -> SOFTWARE -> ORACLE registry or another way for get the installation path for ODAC installed, and then get the tnsnames files path for try parser it.

             

            Data Source is only for a unit test,

            string connstring = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ZZZ.ZZZ.ZZZ.ZZZ)(PORT=1523))" +

                  "(CONNECT_DATA=(SID=oas1d)));User Id=XXXX;Password=XXXX;";


            In .NET applications using Oracle.DataAccess, tnsnames is required.

            • 3. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
              Alex.Keh .Product.Manager-Oracle

              My point was there are multiple ways to set the Path. How you programmatically retrieve it depends on which method you use. I was asking about your choice of method so that I could direct you how to retrieve the info.

               

              Let's assume you use are finding TNS through the DllPath setting in the Windows Registry. If that's the case, you retrieve the value from HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.121.1.0\DllPath to get the Path setting.

              • 4. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
                kiquenet

                The DllPath not the same that TNSNames path.

                 

                A question: TNSNames path is {DllPath}\..\Network\Admin by default ?

                 

                Network\Admin not appears in Windows Registry, HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ and subentries. ONLY maybe in TNS_ADMIN entry.

                 

                How change TNSNames path  without changes DllPath ? Not possible ? Because TNSNames path  dependes always on DllPath. And always the same filename: tnsnames.ora, it cannot be changed.

                 

                 

                For example, for my case

                HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.112.2.0\

                "DllPath"="c:\\app\\Company\\bin"


                Installing Oracle Data Provider for .NET, Unmanaged Driver is good reference about DllPath, but not references anyway about Tnsnames path neiter Network\Admin.


                I guest that Oracle.DataAccess manages DllPath and "Network\Admin" for looking tnsnames.ora (I'm right or wrong?)


                Now, I understand that this DllPath is wrong

                  <add name="DllPath" value="C:\app\Company\odp.net\bin\4"/>


                this DllPath is right

                  <add name="DllPath" value="C:\app\Company\bin"/>


                DllPath is path to  dependent unmanaged DLLs


                In that OTN web reference, how the Oracle.DataAccess.dll searches for dependent unmanaged DLLs (such as Oracle Client) based on the following order:

                 

                1. Directory of the application or executable.

                    

                           This means copy all unmanaged DLLs (oci.dll and more) to the path:

                 

                          bin\Debug path of my Application (Windows Forms for example)

                    

                          bin\  path of my Web Application

                 

                1. DllPath setting specified by application config or web.config.

                 

                <oracle.dataaccess.client>

                  <settings>

                      <add name="DllPath" value="C:\app\Company\bin"/>

                    </settings>

                  </oracle.dataaccess.client>

                 

                 

                1. DllPath setting specified by machine.config.

                <oracle.dataaccess.client>

                  <settings>

                <add name="DllPath" value="C:\app\Company\bin"/>

                          </settings>

                  </oracle.dataaccess.client>

                 

                1. DllPath setting specified by the Windows Registry.HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\version\DllPath
                2. Directories specified by the Windows PATH environment variable.

                 

                Maybe TNS_ADMIN has priority than DllPath\..\Network\Admin ?

                 

                [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_odac]

                "ORACLE_HOME"="c:\\app\\Company\\"

                "ORACLE_HOME_NAME"="odac"

                "NLS_LANG"="SPANISH_SPAIN.WE8MSWIN1252"

                "TNS_ADMIN"="C:\\\\app\\\\Company\\\\Network\\\\Admin\\\\"


                TNS_ADMIN appears in Oracle OTN documentation?


                Which is the order or priority? (A, B, C)


                A)

                <oracle.dataaccess.client>

                           <settings>

                         <add name="DllPath" value="C:\app\Company\bin"/>

                          </settings>

                  </oracle.dataaccess.client>

                 

                B)

                [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_odac]

                "TNS_ADMIN"="C:\\\\app\\\\Company\\\\Network\\\\Admin\\\\"


                C)

                HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.112.2.0\

                "DllPath"="c:\\app\\Company\\bin"


                Thanks a lot.

                • 5. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
                  Alex.Keh .Product.Manager-Oracle

                  That's correct the TNS path is the same as the DllPath. They are largely similar in the context of ODP.NET. DllPath is specific to ODP.NET, but not other Oracle Client components.

                   

                  Here's is the search order for finding unmanaged DLLs:

                  Installing Oracle Data Provider for .NET, Unmanaged Driver

                  • 6. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
                    kiquenet

                    I think, default TNS path (defaults to <OracleHomeDir>\Network\Admin) NOT is the same as the default DllPath (defaults to <OracleHomeDir>\bin)



                    The selected Oracle Home dictates the tnsnames.ora location. It is either:

                     

                    • Defined as TNS_ADMIN environment variable
                    • Defined in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\Key_<xxxx>\TNS_ADMIN
                    • Defined in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\Key_<xxxx>\TNS_ADMIN
                    • Otherwise it defaults to <OracleHomeDir>\Network\Admin


                    Which is official documentation about it in OTN ?


                    PD: Now, I dont know when ODP.NET uses HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE node or when uses HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE node


                    • 7. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
                      Alex.Keh .Product.Manager-Oracle

                      Sorry, I wasn't clearer. For ODP.NET only, the path you define for where to find the TNS file is going to be the same (for the most part) as what you define for DllPath. Other Oracle components do not use DllPath. They will use follow Oracle Net Services precedence order, which is documented here:

                       

                      Local Naming Parameters in the tnsnames.ora File

                      • 8. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
                        kiquenet

                        I still confuse.

                         

                        DllPath not the same path that tnsnames.ora

                         

                        For ODP.NET only, by default


                        DllPath =  ORACLE_BASE\\ORACLE_HOME\bin

                         

                        tnsnames = ORACLE_HOME/network/admin

                         

                        are different paths, I understand that.

                         

                        Folder Root is the same (ORACLE_HOME)

                        DllPath searchs in bin subfolder and  for Tnsnames searchs in network/admin subfolder.

                         

                        About tnsnames

                         

                        By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net will check the other directories for the configuration file. For example, the order checking the tnsnames.ora file is as follows:

                        1. The directory specified by the TNS_ADMIN environment variable. If the file is not found in the directory specified, then it is assumed that the file does not exist.
                        2. If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory.

                        Note:

                        On Microsoft Windows, the TNS_ADMIN environment variable is used if it is set in the environment of the process. If theTNS_ADMIN environment variable is not defined in the environment, or the process is a service which does not have an environment, then Microsoft Windows scans the registry for a TNS_ADMIN parameter.

                         

                        Then, priority order

                         

                        • Defined as TNS_ADMIN environment variable
                        • Defined in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\Key_<xxxx>\TNS_ADMIN
                        • Defined in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\Key_<xxxx>\TNS_ADMIN
                        • Otherwise it defaults to <OracleHomeDir>\Network\Admin


                        Only HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ vs HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\




                        About DllPath


                        ODP.NET consists of managed and unmanaged binaries. Through the use of the DllPath configuration parameter, each application can specify theORACLE_BASE\\ORACLE_HOME\bin location that the dependent unmanaged Oracle Client binaries are loaded from. However, the ORACLE_BASE\\ORACLE_HOME must have the same ODP.NET version installed as the version that the application uses. Otherwise, a version mismatch exception is thrown.

                        The Oracle.DataAccess.dll searches for dependent unmanaged DLLs (such as Oracle Client) based on the following order:

                        1. Directory of the application or executable.
                        2. DllPath setting specified by application config or web.config.
                        3. DllPath setting specified by machine.config.
                        4. DllPath setting specified by the Windows Registry.HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\version\DllPath
                        5. Directories specified by the Windows PATH environment variable.

                        Upon installation of ODP.NET, Oracle Universal Installer sets the DllPath Windows Registry value to the ORACLE_BASE\\ORACLE_HOME\bin directory where the corresponding dependent DLLs are installed. Developers must provide this configuration information on an application-by-application basis.

                        When a new ODP.NET version is installed, default values are set in the Windows Registry for the new version. Because the policy DLLs redirect all ODP.NET references to this new ODP.NET version, applications use the default values. Developers can provide a config or web.config file specific to the application to prevent this redirection. The configuration file settings always apply to the application, regardless of whether or not patches or new versions are installed later.


                        • 9. Re: Get TNSNames path programatically (ODP.NET not Managed) in C#
                          Alex.Keh .Product.Manager-Oracle

                          Only ODP.NET uses DllPath to find its dependent Oracle Client files. Every other Oracle data access driver doesn't use DllPath. They use the conventional Oracle Client path settings.