Getting location of file tnsnames.ora by code

Some years ago I had the same problem.
Back then I had to support Oracle 9 and 10 so the code only takes care of those versions, but maybe it saves you from some research.The idea is to:

• search the registry to determine the oracle client version
• try to find the ORACLE_HOME
• finally get the 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");    }}

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

Of course it is possible to have a working Oracle client without this file. Oracle has bewildering array of networking options, and there are plenty of ways to achieve a working setup with using TNSNAMES. Depending on what you are trying to achieve here, your first port of call might be the sqlnet.ora file, which is also found in %ORACLE_HOME%/network/admin. This should contain a line that looks something like this:

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)

TNSNAMES means it will use the TNSNAMES.ora file (second in this case). LDAP and HOSTNAME are alternate ways of resolving the database. If there is no TNSNAMES the TNSNAMES.ora file will be ignored if it exists in the right place.

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

Environment.GetEnvironmentVariable("ORACLE_HOME");

Environment.GetEnvironmentVariable("TNS_ADMIN");

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); }