Auto-discover Oracle TNS service names with COSidHelper class

li@agileinfollc.com

Rate me:

4.20/5 (11 votes)

5 Sep 20031 min read

71.3K

948

An article on how to discover Oracle TNS service names.

Download demo project - 18.9 Kb

Introduction

This article introduces a class which automatically constructs Oracle TNS service names defined on a local machine.

Background

I always wonder how to find out how many Oracle instances are defined programmatically. Using SQL*Plus, one has to type in the Oracle instance name. In some cases, because we may have a lot of databases, we often make mistakes. One day I decided to write something to automatically discover all the Oracle instances defined on the local machine. So here comes COSidHelper class.

Using the code

This code is very easy to use, you add SidHelper.h and SidHelper.cpp into your project, then when you need to display Oracle TNS service names, simply construct an instance of this class like this:

COSidHelper osidHelper;
for( int nIndex =0; nIndex < osidHelper.m_strArray.GetSize(); nIndex++ )
{
    // ...
}

For test purposes, I also put in a Display() method there.

Points of interest

Check directory existence. In older Oracle client installs, tnsnames.ora is under net80, in newer version, it is under network directory.
RegistryKey operation.
Parser for Oracle tnsnames.ora file structure.

History

I wrote this class long time ago for a software called PowerQuery). Today finally got time to share it with CodeProject community. You can use this class for free.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

li@agileinfollc.com

Web Developer

United States

LI is a architect of several database-centric tools and technologies. He has been programming since 1995. He is a Microsoft Certified Solution Developer (MCSD), MCSD.NET, SCJP, SCJD and OCDBA.

His programming experience includes C/C++, C#, MFC, ASP, VB and Perl. He has worked on Solaris, AIX, HPUX and various Windows, and found Windows is the easiest one to work with.

He has over 8 years of database experiences in Oracle, SQL Server, DB2 and other DBMS.

LI co-founded AgileInfoSoftware LLC (http://www.agileinfollc.com) in 2003. He is responsible for the overall vision and development strategy of the company.

COSidHelper::COSidHelper() { CString strPath; CString strFileName; CString strLine; CString strBlock; int nIndex; int nCountOpen = 0; int nCountClose = 0; CStdioFile f; // Step 1. Get Oracle Home Path GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\KEY_OraClient10g_home1"), _T("ORACLE_HOME"), strPath ); if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\KEY_OraDb10g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("\\SOFTWARE\\Wow6432Node\\ORACLE\\KEY_OraClient10g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("\\SOFTWARE\\Wow6432Node\\ORACLE\\KEY_OraDb10g_home1"), _T("ORACLE_HOME"), strPath ); } // fix to Oralce11g by LiXiaoQing at 2016-06-15 16:05:00 start if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\KEY_OraClient11g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\KEY_OraDb11g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("\\SOFTWARE\\Wow6432Node\\ORACLE\\KEY_OraClient11g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("\\SOFTWARE\\Wow6432Node\\ORACLE\\KEY_OraDb11g_home1"), _T("ORACLE_HOME"), strPath ); } // fix to Oralce11g by LiXiaoQing at 2016-06-15 16:05:00 end // fix to Oralce12g by LiXiaoQing at 2016-06-15 16:05:00 start if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\KEY_OraClient12g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\KEY_OraDb12g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("\\SOFTWARE\\Wow6432Node\\ORACLE\\KEY_OraClient12g_home1"), _T("ORACLE_HOME"), strPath ); } if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("\\SOFTWARE\\Wow6432Node\\ORACLE\\KEY_OraDb12g_home1"), _T("ORACLE_HOME"), strPath ); } // fix to Oralce12g by LiXiaoQing at 2016-06-15 16:05:00 end // Step 2. Check whether tnsnames.ora file exists or not strFileName.Format( _T("%s\\network\\admin\\tnsnames.ora"), strPath ); if( (_taccess( strFileName, 0 )) == -1 ) { // File does not exists in this case strFileName.Format( _T("%s\\net80\\admin\\tnsnames.ora"), strPath ); if( (_taccess( strFileName, 0 )) == -1 ) { // One more try GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\ALL_HOMES\\ID0"), _T("PATH"), strPath ); if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\Wow6432Node\\ORACLE\\ALL_HOMES\\ID0"), _T("PATH"), strPath ); } strFileName.Format( _T("%s\\network\\admin\\tnsnames.ora"), strPath ); if( (_taccess( strFileName, 0 )) == -1 ) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\ORACLE\\ALL_HOMES\\ID0"), _T("PATH"), strPath ); if (strPath.IsEmpty()) { GetRegKeyValue( HKEY_LOCAL_MACHINE, _T("SOFTWARE\\Wow6432Node\\ORACLE\\ALL_HOMES\\ID0"), _T("PATH"), strPath ); } strFileName.Format( _T("%s\\net80\\admin\\tnsnames.ora"), strPath ); } } } // Step 3. If exists, then process it if( f.Open( strFileName, CFile::typeText ) ) { while( f.ReadString( strLine ) ) { strLine.TrimLeft(); strLine.TrimRight(); if ( strLine.Left( 1 ) == _T("#") || strLine.IsEmpty() ) { // This line is comment, skip it continue; } strBlock += strLine; for( nIndex = 0; nIndex < strLine.GetLength(); nIndex ++ ) { switch( strLine[nIndex] ) { case '(': nCountOpen++; break; case ')': nCountClose++; break; } } if ( nCountOpen == nCountClose && nCountOpen > 0 ) { // Now this is a complete block, need to pricess this block strLine = strBlock.Left( strBlock.Find( _T("=") ) ); strLine.TrimRight(); m_strArray.Add( strLine ); strBlock.Empty(); nCountOpen = 0; nCountClose = 0; } } f.Close(); } }

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.