使用Powershell访问远程Oracle数据库 [英] Access remote Oracle database with Powershell

问题描述

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

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.

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

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.

我尝试使用ODAC(我认为我必须安装Visual Studio才能使用它，因为安装失败).我听说OleBD可能会容易得多.我想用TNS做到这一点是可能的.有人可以在这里给我任何指导吗?我有一本关于Powershell和Oracle的书，但我仍然感到困惑，我无法超越第一阶段.

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.

任何帮助将不胜感激.

推荐答案

下面是我2015年使用的小例子.

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 assembly
Add-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 = 0
while ($reader1.read())
{
  $reader1["XX_MYSESSION_ID"]  
}

# Fermeture de la conexion
$reader1.Close()
$oraConn.Close()

Write-Output $retObj

-----编辑于2017年秋季-----

一段时间以来，Oracle编辑了.NET的完整托管DLL，可通过Nugets获得它:

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 = 0
while ($reader1.read())
{
  $reader1["XX_MYSESSION_ID"]  
}

# Fermeture de la conexion
$reader1.Close()
$oraConn.Close()

Write-Output $retObj

这篇关于使用Powershell访问远程Oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！