Getting Oracle TNS Names for your Connect to Database Form
Until recently I was asking my users to type in the TNS name of the Oracle Service they want to connect to. This was a bit tedious for them as they have to first remember all TNS names they want to use, secondly they have to keep them handy to paste in the connect form.
So, I decided to give them some relief. I queried the internet and came across various methods, some worked, some failed, and some were working only for certain versions of Oracle. So, ultimately I decided to make my own version which can work with 9i, and 10g and here it goes.
I query the Registry to know the path of TNSNames.ora file.
I found that different version of Oracle store ORACLE_HOME at different places
10g
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_Ora10gClient
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_client
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
9i
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
So, I decided to read all of them starting with the word KEY_ in case of 10g and ORACLE in case of 9i
and then iterate through each one’s child to find if it contains ORACLE_HOME and where ever this is found, take the value and add NETWORK/ADMIN/TNSNames.ora, and see if a file exists. If yes, parse the file for TNS Name else just keep iterating.
So, this is the first function, GetTnsNames browse through the registry.
GetTNSFromOracleHome, just arranges them in to one arraylist from one TnsNames.ora files
ExtractTnsNamesFromTnsNamesOra, parses the TnsNames.ora file.
public ArrayList GetTnsNames()
{
ArrayList tnsNames = new ArrayList();
string oracleHome = “”;
RegistryKey masterKey = Registry.LocalMachine.OpenSubKey(@”SOFTWARE\ORACLE”);
if (masterKey == null)
{
return null;
}
string[] subKeyName = masterKey.GetSubKeyNames();
for (int i = 0; i < subKeyName.Length; i++)
{
// Oracle 10g version
if (subKeyName[i].StartsWith(“KEY_”))
{
try
{
RegistryKey subKey = masterKey.OpenSubKey(subKeyName[i]);
oracleHome = subKey.GetValue(“ORACLE_HOME”).ToString();
subKey.Close();
tnsNames.AddRange(FillTNS(oracleHome));
}
catch (NullReferenceException)
{
//Since ORACLE_HOME may not be there
}
}
}
// Oracle 9i version
try
{
oracleHome = masterKey.GetValue(“ORACLE_HOME”).ToString();
tnsNames.AddRange(GetTNSFromOracleHome(oracleHome));
}
catch (NullReferenceException)
{
//Since ORACLE_HOME may not be there
}
masterKey.Close();
return tnsNames;
}
private ArrayList GetTNSFromOracleHome(string oracleHome)
{
ArrayList cbTNSNames = new ArrayList();
string[] tnsNames = ExtractTnsNamesFromTnsNamesOra(oracleHome);
if (tnsNames != null)
{
for (int i = 0; i < tnsNames.Length; i++)
{
if (!string.IsNullOrEmpty(tnsNames[i]))
{
cbTNSNames.Add(tnsNames[i]);
}
}
}
return cbTNSNames;
}
private string[] ExtractTnsNamesFromTnsNamesOra(string oracleHomePath)
{
StringBuilder output = new StringBuilder();
string fileLine;
Stack parens = new Stack();
// open tnsnames.ora
StreamReader sr = null;
try
{
sr = new StreamReader(oracleHomePath + “\\NETWORK\\ADMIN\\tnsnames.ora”);
}
catch (System.IO.FileNotFoundException)
{
return null;
}
if (sr == null) return null;
// Read the first line of the file
fileLine = sr.ReadLine();
// loop through, reading each line of the file
while (fileLine != null)
{
// if the first non whitespace character is a #, ignore the line
// and go to the next line in the file
if (fileLine.Length > 0 && !string.IsNullOrEmpty(fileLine.Trim()) && fileLine.Trim().Substring(0, 1) != “#”)
{
// Read through the input line character by character
char lineChar;
for (int i = 0; i < fileLine.Length; i++)
{
lineChar = fileLine[i];
if (lineChar == ‘(‘)
{
// if the char is a ( push it onto the stack
parens.Push(lineChar);
}
else if (lineChar == ‘)’)
{
// if the char is a ), pop the stack
parens.Pop();
}
else
{
// if there is nothing in the stack, add the character to the ouput
if (parens.Count == 0)
{
output.Append(lineChar);
}
}
}
}
// Read the next line of the file
fileLine = sr.ReadLine();
}
// Close the stream reader
sr.Close();
// Split the output string into a string[]
string[] split = output.ToString().Split(‘=’);
// trim each string in the array
for (int i = 0; i < split.Length; i++)
{
split[i] = split[i].Trim();
}
Array.Sort(split);
return split;
}
Enjoy!!
