Powershell与Oracle数据库的连接

我不确定这在技术上是否是一个解决方案——我更愿意将其归类为一种变通方法,但它对我来说很有效。

在做了更多的研究后,我找到了一个合适的替代方案 Oracle.ManagedDataAccess.dll .我找到了 System.Data.OracleClient Net Framework的类。(它需要安装一个Oracle客户端,幸运的是机器已经安装了该客户端)

以下是对我有效的解决方案的概述:

add-type -AssemblyName System.Data.OracleClient

$username = "XXXX"
$password = "XXXX"
$data_source = "XXXX"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"

$statement = "select level, level + 1 as Test from dual CONNECT BY LEVEL <= 10"

try{
    $con = New-Object System.Data.OracleClient.OracleConnection($connection_string)

    $con.Open()

    $cmd = $con.CreateCommand()
    $cmd.CommandText = $statement

    $result = $cmd.ExecuteReader()
    # Do something with the results...

} catch {
    Write-Error (“Database Exception: {0}`n{1}” -f `
        $con.ConnectionString, $_.Exception.ToString())
} finally{
    if ($con.State -eq ‘Open’) { $con.close() }
}

使用服务器:端口/服务语法。

$dataSource="server.network:1522/service1.x.y.z"

这是我的(Powershell,但您可以使用C#)代码来使用ODP实现这一点。NET managed data access…。我使用Powershell Add类型直接加载它,但在C中是使用/引用。。。

function New - OracleConnection { <
  #
  .SYNOPSIS# generate a well - formed connection string with individual properties
  Create and open a new Oracle connection using optional connectionstring argument

    .DESCRIPTION
  Create and open a new Oracle connection using optional connectionstring argument

    .EXAMPLE
  New - OracleConnection
  New - OracleConnect - connectionString "My well-formed Oracle connections string"

    .NOTES
  Connection is opened here by
  default# > #Add - Type - Path ".\Oracle.ManagedDataAccess.dll" [OutputType([Oracle.ManagedDataAccess.Client.OracleConnection])]
  Param(
    [Parameter(Mandatory = $false)]
    [string] $theConnectionString, [Parameter(Mandatory = $false)]
    [string] $openOnCreate = "1"#
    means true - open the connection...
  )[Oracle.ManagedDataAccess.Client.OracleConnection] $con = New - Object - TypeName Oracle.ManagedDataAccess.Client.OracleConnection;
  if ([string]::IsNullOrEmpty($theConnectionString)) {#
    $dataSource = "*********:1521/******;"

    $conStringBuilder = New - Object Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder;
    $conStringBuilder["Data Source"] = $dataSource;
    $conStringBuilder["User ID"] = "*******";
    $conStringBuilder["Password"] = "*******";
    $conStringBuilder["Persist Security Info"] = $True;
    $conStringBuilder["Connection Lifetime"] = 180;
    $conStringBuilder["Connection Timeout"] = 10;
    $conStringBuilder["Pooling"] = $true;
    $conStringBuilder["Min Pool Size"] = 10;
    $conStringBuilder["Max Pool Size"] = 20;
    $conStringBuilder["Incr Pool Size"] = 5;
    $conStringBuilder["Decr Pool Size"] = 2;
    $conStringBuilder["Statement Cache Size"] = 200;
    $conStringBuilder["Statement Cache Purge"] = $false;#
    default

    $con.ConnectionString = $conStringBuilder.ConnectionString;
  } else {
    $con.ConnectionString = $theConnectionString;
  }

  if (Get - IsTrue - yesNoArg $openOnCreate) {
    if (-not(Get - ConnectionStateIsOpen($con))) {#
      attempt open, ignore error
      if already open.State is normally open after successful create
      try {
        $con.Open();
      } catch {
        $null;
      }
    }
  }

  Write - Output - NoEnumerate $con;

}

2. 获取连接:

$con = New - OracleConnection;

3. 根据需要打开/关闭(默认为打开…)

... if($con.State-ne[系统数据连接状态]::打开){ $con.Open()#

4. 使用它,然后关闭/处置。。。

    $sql = "SELECT * FROM YOUR_TABLE t WHERE t.SOMETHING = :the_parm";    
        [Oracle.ManagedDataAccess.Client.OracleCommand]$cmd = New-Object -TypeName Oracle.ManagedDataAccess.Client.OracleCommand;    
        $cmd.Connection = Get-OracleConnection;
        $cmd.CommandText = $sql;
        $cmd.BindByName = $true;
        $dbType = ([Oracle.ManagedDataAccess.Client.OracleDbType]::VarChar2);
       
        $cmd.Parameters.Add(( New-Param -name "the_parm" -type $dbType -size 15  )).Value = $the_criteria_value  ;
        Write-Output -NoEnumerate ($cmd.ExecuteScalar());
        $cmd.Connection.Close();
        $cmd.Dispose();
        

这很可能是oracle 12.2.x的一个问题
SQLNET.ALLOWED\u LOGON\u VERSION\u CLIENT=8 SQLNET.ALLOWED\u LOGON\u VERSION\u SERVER=8

我遇到了完全相同的问题,我从ODAC版本12.2.0.1.0切换到了版本11.2.O.3,打开连接和读取数据就像一种魅力。希望这有帮助。 谢谢