使用 Powershell 访问远程 Oracle 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30697794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access remote Oracle database with Powershell
提问by JamesPy
I need to be able to connect to an Windows 7 based Oracle server (32 bit, Oracle XE) which is on my network. The machine I need to connect from is running Windows 7 64 bit, with Powershell installed on both machines.
我需要能够连接到我网络上的基于 Windows 7 的 Oracle 服务器（32 位，Oracle XE）。我需要连接的机器运行的是 Windows 7 64 位，两台机器上都安装了 Powershell。
I have installed the Oracle 32 bit client on my 64 bit machine and have SQL Developer installed on both machines. I want to create a script that connects the the Oracle database and runs a simple SELECT query. I can't get it to connect though.
我已经在我的 64 位机器上安装了 Oracle 32 位客户端，并且在两台机器上都安装了 SQL Developer。我想创建一个脚本来连接 Oracle 数据库并运行一个简单的 SELECT 查询。我无法让它连接。
I have tried using ODAC (I think I have to install Visual Studio to use this as the install fails). I hear that OleBD might be a lot easier. I would like to do it with TNS is possible. Can anyone offer me any guidance here? I have a book on Powershell and Oracle and I am still confused, I can't get past the first stage.
我曾尝试使用 ODAC（我想我必须安装 Visual Studio 才能使用它，因为安装失败）。我听说 OleBD 可能要容易得多。我想用TNS来做是可能的。任何人都可以在这里为我提供任何指导吗？我有一本关于 Powershell 和 Oracle 的书，但我仍然很困惑，我无法通过第一阶段。
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by JPBlanc
Here is a small example of what I was using in 2015.
这是我在 2015 年使用的一个小例子。
# Ora002.ps1
# Need installation of ODAC1120320Xcopy_x64.zip
# The 32 bit version also exists
# Load the good assembly
Add-Type -Path "C:\oracle\odp.net\bin\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 = 0
while ($reader1.read())
{
$reader1["XX_MYSESSION_ID"]
}
# Fermeture de la conexion
$reader1.Close()
$oraConn.Close()
Write-Output $retObj
----- Edited in fall 2017 -----
----- 2017 年秋季编辑 -----
For a while now Oracle edited a full managed DLL for .NET which is available through Nugets :
一段时间以来，Oracle 为 .NET 编辑了一个完整的托管 DLL，可通过 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 = 0
while ($reader1.read())
{
$reader1["XX_MYSESSION_ID"]
}
# Fermeture de la conexion
$reader1.Close()
$oraConn.Close()
Write-Output $retObj
回答by Saurabh Soni
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.
我已经用 Oracle DLL 路径更新了上面的代码。当我们从 Powershell 连接 Oracle 时，我们连接到托管 Oracle 服务 DLL，可以在下面提到的路径中找到。
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 assembly
Add-Type -Path "C:\app\ssz\product.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
回答by Ostati
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 libraryDLL, just make sure you have the required DLL file under the lib folder.
接受的答案依赖于进行客户端安装，并且随着 Oracle 发布了新的托管版本，它也已过时。您可以使用.NET Oracle 库DLL，只需确保 lib 文件夹下有所需的 DLL 文件。
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 = $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()
}
