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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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)))) | |
| ) | |
| ) | |
| ) | |
| ) |
I hope this helps someone.
Until then
Keep scripting
Thom
ScriptPosse 