Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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))))
)
)
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.