Getting the location of the tnsnames.ora file by code

Question

How can I get the location of the tnsnames.ora file by code on a machine with an Oracle client installed?

Is there a Windows registry key indicating the location of this file?

Answer 1

A few years ago I had the same problem.
At that time, I had to support Oracle 9 and 10, so the code only cares about these versions, but maybe this will save you some research. The idea is as follows:

• search the registry to determine the version of the oracle client.
• try to find ORACLE_HOME
• finally get tnsnames from HOME

---

 public enum OracleVersion { Oracle9, Oracle10, Oracle0 }; private OracleVersion GetOracleVersion() { RegistryKey rgkLM = Registry.LocalMachine; RegistryKey rgkAllHome = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\ALL_HOMES"); /* * 10g Installationen don't have an ALL_HOMES key * Try to find HOME at SOFTWARE\ORACLE\ * 10g homes start with KEY_ */ string[] okeys = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE").GetSubKeyNames(); foreach (string okey in okeys) { if (okey.StartsWith("KEY_")) return OracleVersion.Oracle10; } if (rgkAllHome != null) { string strLastHome = ""; object objLastHome = rgkAllHome.GetValue("LAST_HOME"); strLastHome = objLastHome.ToString(); RegistryKey rgkActualHome = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE\HOME" + strLastHome); string strOraHome = ""; object objOraHome = rgkActualHome.GetValue("ORACLE_HOME"); string strOracleHome = strOraHome = objOraHome.ToString(); return OracleVersion.Oracle9; } return OracleVersion.Oracle0; } private string GetOracleHome() { RegistryKey rgkLM = Registry.LocalMachine; RegistryKey rgkAllHome = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\ALL_HOMES"); OracleVersion ov = this.GetOracleVersion(); switch(ov) { case OracleVersion.Oracle10: { string[] okeys = rgkLM.OpenSubKey(@"SOFTWARE\ORACLE").GetSubKeyNames(); foreach (string okey in okeys) { if (okey.StartsWith("KEY_")) { return rgkLM.OpenSubKey(@"SOFTWARE\ORACLE\" + okey).GetValue("ORACLE_HOME") as string; } } throw new Exception("No Oracle Home found"); } case OracleVersion.Oracle9: { string strLastHome = ""; object objLastHome = rgkAllHome.GetValue("LAST_HOME"); strLastHome = objLastHome.ToString(); RegistryKey rgkActualHome = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\ORACLE\HOME" + strLastHome); string strOraHome = ""; object objOraHome = rgkActualHome.GetValue("ORACLE_HOME"); string strOracleHome = strOraHome = objOraHome.ToString(); return strOraHome; } default: { throw new Exception("No supported Oracle Installation found"); } } } public string GetTNSNAMESORAFilePath() { string strOracleHome = GetOracleHome(); if (strOracleHome != "") { string strTNSNAMESORAFilePath = strOracleHome + @"\NETWORK\ADMIN\TNSNAMES.ORA"; if (File.Exists(strTNSNAMESORAFilePath)) { return strTNSNAMESORAFilePath; } else { strTNSNAMESORAFilePath = strOracleHome + @"\NET80\ADMIN\TNSNAMES.ORA"; if (File.Exists(strTNSNAMESORAFilePath)) { return strTNSNAMESORAFilePath; } else { throw new SystemException("Could not find tnsnames.ora"); } } } else { throw new SystemException("Could not determine ORAHOME"); } } 

Answer 2

On Windows, the most likely locations are either %ORACLE_HOME%/network/admin , or %TNS_ADMIN% (or the TNS_ADMIN registry setting). These two panels cover almost every installation.

Of course, the working Oracle client can work with this file. Oracle has many networking capabilities, and there are many ways to achieve operational setup using TNSNAMES. Depending on what you are trying to achieve here, your first port of call may be the sqlnet.ora file, which is also located in %ORACLE_HOME%/network/admin . This should contain a line that looks something like this:

 NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME) 

TNSNAMES means that it will use the TNSNAMES.ora file (the second in this case). LDAP and HOSTNAME are alternative ways to resolve the database. If there is no TNSNAMES , the TNSNAMES file will be ignored if it exists in the right place.

In C # /. NET, this should get the environment variables:

Environment.GetEnvironmentVariable("ORACLE_HOME");

Environment.GetEnvironmentVariable("TNS_ADMIN");

Answer 3

 List<string> logicalDrives = Directory.GetLogicalDrives().ToList(); List<string> result = new List<string>(); foreach (string drive in logicalDrives) { Console.WriteLine("Searching " + drive); DriveInfo di = new DriveInfo(drive); if(di.IsReady) result = Directory.GetFiles(drive, "tnsnames.ora", SearchOption.AllDirectories).ToList(); if (0 < result.Count) return; } foreach (string file in result) { Console.WriteLine(result); } 

Answer 4

According to the network, which depends on the version of Oracle and the working directory of the SQL * Plus process. This first link tells you an environment variable that indicates the base path for some versions of (7, 8, 9i) Oracle. If you are using another, I am sure there is a similar way to access the system directory.

If you distribute versions of these files everywhere, and rely on the "look for local tnsnames.ora behavior" of the client, then I think you're out of luck.

Answer 5

I am not a C # or Windows guy because I hope this helps. The tnsnames.ora file must be located in:

 ORACLE_HOME\network\admin 

If an alternate location is specified, it must be accessible through the TNS_ADMIN registry key.

See the link for more information on how Oracle handles tns names on Windows.