使用Powershell访问远程Oracle数据库

Question

我需要能够连接到基于Windows 7的Oracle服务器(32位，Oracle XE)，这是在我的网络上。我需要连接的机器运行的是Windows 7 64位，两台机器上都安装了Powershell。

我已经在我的64位机器上安装了Oracle 32位客户端，并在两台机器上安装了SQL Developer。我想创建一个连接Oracle数据库的脚本，并运行一个简单的SELECT查询。但是我无法连接。

我尝试过使用ODAC(我想我必须安装Visual Studio来使用这个安装失败)。我听说olbd可能会简单得多。我想用TNS来做是可能的。有人能给我点指导吗?我有一本关于Powershell和Oracle的书，但我仍然很困惑，我无法通过第一阶段。

Answer 1

这是我在2015年使用的一个小例子。

# Ora002.ps1
# Need installation of ODAC1120320Xcopy_x64.zip 
# The 32 bit version also exists
# Load the good assembly
Add-Type -Path "C:oracleodp.netbin4Oracle.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 = 0
while ($reader1.read())
{
  $reader1["XX_MYSESSION_ID"]  
}
# Fermeture de la conexion
$reader1.Close()
$oraConn.Close()
Write-Output $retObj

——编辑2017年秋天——

一段时间以来，Oracle为。net编辑了一个完整的托管DLL，可以通过Nugets获得:

# Download the package if it's not on the disk    
$version = '12.2.1100'
try
{
  if (! $(Test-Path ".NugetPackagesOracle.ManagedDataAccess.$versionlibnet40Oracle.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 ".NugetPackagesOracle.ManagedDataAccess.$versionlibnet40Oracle.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 = 0
while ($reader1.read())
{
  $reader1["XX_MYSESSION_ID"]  
}
# Fermeture de la conexion
$reader1.Close()
$oraConn.Close()
Write-Output $retObj

Answer 2

我已经用Oracle DLL路径更新了上面的代码。当我们从Powershell连接Oracle时，我们连接到托管Oracle服务DLL，它可以在下面提到的路径中找到。

可能是我错了，但下面的代码为我工作。

cls
# Ora002.ps1
# Need installation of ODAC1120320Xcopy_x64.zip 
# The 32 bit version also exists
# Load the good assembly
Add-Type -Path "C:appsszproduct12.1.0client_1odp.netmanagedcommonOracle.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

Answer 3

可接受的答案依赖于做客户端安装，而且由于Oracle已经发布了一个新的托管版本，它也过时了。你可以使用。net Oracle库DLL，只要确保在lib文件夹下有所需的DLL文件。

Add-Type -Path "libOracle.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 = $cn
try {
    $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()
}