Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Query Oracle SQL DB using PowerShell and ODAC.NET (so no Oracle Client required). Associated blogpost
# Path to ODAC.NET Installation
Add-Type -Path "c:\ODAC\\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"
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionstring)
$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.