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:

I hope this helps someone.

 

Until then

 

Keep scripting

 

Thom

 

Advertisements