Using the .Net Oracle Data Access Client

There is a cmdlet for SQL Server databases called Invoke-Sqlcmd that will allow you to run TSQL commands but there is not a cmdlet for Oracle. This is where the .Net framework comes to the rescue.

Microsoft no longer provides the Oracle library. So we need to download it from the Oracle website link below

https://www.oracle.com/database/technologies/dotnet-odacmsi-vs2017-downloads.html

Then, we can use Add-Type to add the .Net class to a PowerShell session, and use a basic .Net framework syntax to execute database commands, as shown below.

Add-Type -Path 'C:\Program Files (x86)\Oracle Developer Tools for VS2017\odp.net\managed\common\Oracle.ManagedDataAccess.dll'

$conString = "User Id=myuser;Password=mypassword;Data Source=mydatabase"
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection
$con.ConnectionString = $conString
$con.Open()

$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
$cmd.Connection = $con
$cmd.CommandText = 'select banner from v$version'
$rs = $cmd.ExecuteReader()

while ($rs.Read()) 
{
    Write-Host "$($rs.GetValue(0))"
}

$con.Close()
$con.Dispose()