TNSNames File Parsing


If you’ve ever worked with  Oracle you are familiar with Oracle’s TNSNAMES file. This file describes how to get to a database.   With ODP.Net it doesn’t provide a means to parse the TNSNAMES.ora file and then in turn use it with ODP.Net.  From everything I’ve read you must just copy from the Description() and put Data Source = Description(). Then you can use that as a means to connect to your Oracle Database server.    With that in mind I set out to write some scripting to help with this problem.

The first thing I did was to follow this great article by the Scripting Guys about how to use ODP.NET.  After reading that article I found a great module on the Gallery that implemented much of what is spoken about there and I’ll be using that module here in this posting (SimplySQL)

Now I know where my  TNSNAMES.ora file is located so I’ll bring it into my session with:

$tnsnamesPath = 'c:\tns\tnsnames.ora'
$tn = get-content $tnsnamesPath -raw

I brought the file in -raw so that I knew I would have a full object.  Now with some REGEX I can get this file in to the fashion I want.  First to look for the common string in my TNSNAMES.ora file somename = (Description = . 

$parsedTN = $tn -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='

Now that I have the connection Name replaced with Data Source = I can now split it into an array and then select my connection based on that array:

$splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)' 
$splitTN.count
3

$splitTN[1]
Data Source = (DESCRIPTION =
 (ADDRESS_LIST=
 (ADDRESS = (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = server58)(PORT = 1521)))
 (LOAD_BALANCE = YES)(CONNECTION_TIMEOUT=5)(RETRY_COUNT=3)
 (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ketchup)
 (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)))
 )

Now that  I have the connections split into an array I can now select the one I want using Where-Object -like “myconnectionName“.  Then with this handy commandlet Open-OracleConnection From this module (simplySQL) , all i have to do next is pass in my username and password and that should open My oracle connection.

$tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
$connstring = "$tnsnames;User Id=$username;Password=$password"
Open-OracleConnection -ConnectionString $connstring -ConnectionName $connectionName

Below is the full script in a GitHub Gist:

param($tnsnamesPath = 'c:\tns\tnsnames.ora',$username = 'user',$password = 'gotmehere', $connectionName = 'mustard', $query = 'Select sysdate from dual')
$simplySQLPath = (Get-Module ListAvailable simplySQL).ModuleBase
if($simplySQLPath -and (test-path $tnsnamesPath PathType Leaf) -and (![string]::IsNullOrEmpty($node)))
{
[System.Reflection.Assembly]::LoadFile("$simplySQLPath\DataReaderToPSObject.dll") | OUT-NULL
Import-Module SimplySql Force
$parsedTN = (get-content $tnsnamesPath raw) -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='
$splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)'
$tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
$connstring = "$tnsnames;User Id=$username;Password=$password"
try
{
Open-OracleConnection ConnectionString $connstring ConnectionName $connectionName
$result = Invoke-SqlQuery ConnectionName $connectionName Query "$SQLQuery"
Close-SqlConnection ConnectionName $connectionName
}
catch
{
$_.exception
}
}
Else
{
if(!(test-path $tnsnamesPath PathType Leaf ErrorAction Ignore))
{
Throw "Check TNSnamesPath: $tnsNamesPath"
}
else
{
Throw "Exeception SIMPLYSQL not found in module Path $($env:PSModulePath)"
}
}
$result

ketchup=(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server58)(PORT = 1521)))
(LOAD_BALANCE = YES)(CONNECTION_TIMEOUT=5)(RETRY_COUNT=3)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ketchup)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)))
)
mustard =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(LOAD_BALANCE = YES)(CONNECTION_TIMEOUT=5)(RETRY_COUNT=3)
(FAILOVER = on)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mustard)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))
)
)
)
)

view raw
tnsnames.ora
hosted with ❤ by GitHub

I hope this helps someone.

 

Until then

 

Keep scripting

 

Thom

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s