Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Query Oracle SQL DB using PowerShell and ODAC.NET (so no Oracle Client required). Associated blogpost https://blog.darrenjrobinson.com/using-powershell-to-query-oracle-dbs-without-using-the-oracle-client-oracle-data-provider-for-net/
# Path to ODAC.NET Installation
Add-Type -Path "c:\ODAC\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
$reader = $false
# SQL DB Username, Password and DataSource Alias (as per tnsnames.ora)
$username = "SQLQueryUserName"
$password = "SQLUserPassword"
# Alias from TNSNames.ora
$datasource = "IDM"
$connectionstring = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$queryStatment = "Select 'Hello world!' Greeting from dual"
try{
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionstring)
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandType = "text"
$cmd.CommandText = $queryStatment
$reader = $cmd.ExecuteReader()
if ($reader.Read()) {
$result = $reader.GetString(0)
write-host -ForegroundColor Green $result
} else {
write-host -ForegroundColor Yellow $result "nothing returned"
}
} catch {
Write-Error (“Database Exception: {0}`n{1}” -f `
$con.ConnectionString, $_.Exception.ToString())
} finally{
if ($con.State -eq ‘Open’) { $con.close() }
}
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.