Access remote Oracle database with Powershell

Here is a small example of what I was using in 2015.

# Ora002.ps1# Need installation of ODAC1120320Xcopy_x64.zip # The 32 bit version also exists# Load the good assemblyAdd-Type -Path "C:\oracle\odp.net\bin\4\Oracle.DataAccess.dll"# Connexion string$compConStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.213.5.123)(PORT=1609)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=COMPEIERE)));User Id=TheLogin;Password=ThePassword;"# Connexion$oraConn= New-Object Oracle.DataAccess.Client.OracleConnection($compConStr)$oraConn.Open()# Requête SQL$sql1 = @"SELECT XX_MYSESSION_ID FROM XX_SILOGIXWSLOG   WHERE xx_name='customer_log'  AND xx_param_4 IS NOT NULL"@$command1 = New-Object Oracle.DataAccess.Client.OracleCommand($sql1,$oraConn)# Execution$reader1=$command1.ExecuteReader()$n = 0while ($reader1.read()){  $reader1["XX_MYSESSION_ID"]  }# Fermeture de la conexion$reader1.Close()$oraConn.Close()Write-Output $retObj

----- Edited in fall 2017 -----

For a while now Oracle edited a full managed DLL for .NET which is available through Nugets :

# Download the package if it's not on the disk    $version = '12.2.1100'try{  if (! $(Test-Path ".\NugetPackages\Oracle.ManagedDataAccess.$version\lib\net40\Oracle.ManagedDataAccess.dll"))  {    $ManagedDataAccess = Install-Package Oracle.ManagedDataAccess -Destination ".\NugetPackages" -Force -Source 'https://www.nuget.org/api/v2' -ProviderName NuGet -RequiredVersion $version -ErrorAction SilentlyContinue  }  Add-Type -Path ".\NugetPackages\Oracle.ManagedDataAccess.$version\lib\net40\Oracle.ManagedDataAccess.dll"}catch [System.Management.Automation.ParameterBindingException]{  $global:OracleError = New-Object PSCustomObject -Property @{"StackTrace"=$_.ScriptStackTrace;"Detail" = "Ligne $($_.InvocationInfo.ScriptLineNumber) : $($_.exception.message)";"TimeStamp"=([datetime]::Now)}  $log = $null}# Connexion$oraConn= New-Object Oracle.ManagedDataAccess.Client.OracleConnection (($compConStr)$oraConn.Open()# Requête SQL$sql1 = @"SELECT XX_MYSESSION_ID FROM XX_SILOGIXWSLOG   WHERE xx_name='customer_log'  AND xx_param_4 IS NOT NULL"@$command1 = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($sql1,$oraConn)# Execution$reader1=$command1.ExecuteReader()$n = 0while ($reader1.read()){  $reader1["XX_MYSESSION_ID"]  }# Fermeture de la conexion$reader1.Close()$oraConn.Close()Write-Output $retObj

I have Updated the Above code with the Oracle DLL path.While we connect with Oracle from Powershell We connect to Managed Oracle service DLL, which can be found on the path Mentioned below.

May be I could be wrong but the below code worked for me.

cls# Ora002.ps1# Need installation of ODAC1120320Xcopy_x64.zip # The 32 bit version also exists# Load the good assemblyAdd-Type -Path "C:\app\ssz\product\12.1.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"# Production connexion string$compConStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*<Valid Host>*)(PORT=*<Valid Port>*)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=*<SErviceNameofDB>*)));User Id=*<User Id>*;Password=*<Password>*;"# Connexion$oraConn= New-Object Oracle.ManagedDataAccess.Client.OracleConnection($compConStr)$oraConn.Open()# Requête SQL$sql1 = @"SELECT col FROM tbl1 WHERE col1='test'"@$command1 = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($sql1,$oraConn)# Execution$reader1=$command1.ExecuteReader()while ($reader1.read()){  $reader1["col"]  }# Fermeture de la conexion$reader1.Close()$oraConn.Close()Write-Output $retObj

Accepted answer has a dependency to do client install and it's also outdated as Oracle has released a new managed version. You can use .NET Oracle library DLL, just make sure you have the required DLL file under the lib folder.

Add-Type -Path "lib\Oracle.ManagedDataAccess.dll"$query = "select  1 as Col1, 2 as Col2, 3 as Col3 from dual          union          select  4 as Col1, 5 as Col2, 6 as Col3 from dual          union          select  7 as Col1, 8 as Col2, 9 as Col3 from dual"$cn   = New-Object Oracle.ManagedDataAccess.Client.OracleConnection -ArgumentList "TNS-ConnectionString-Here"$cmd  = New-Object Oracle.ManagedDataAccess.Client.OracleCommand    -ArgumentList $query$cmd.Connection = $cntry {    $cn.Open()    $reader = $cmd.ExecuteReader()    while ($reader.Read()) {        $col1 = $reader["Col1"]        $col2 = $reader["Col2"]        $col3 = $reader["Col3"]        Write-Host $col1, $col2, $col3    }    $reader.Dispose()} catch {    Write-Error $_.Exception.Message} finally {    $cmd.Dispose()    $cn.Dispose()}