Powershell 连接 Oracle 数据库

Question

Powershell 连接 Oracle 数据库

9

我在使用Oracle.ManagedDataAccess.dll时，无法从Powershell连接到Oracle数据库。

我按照Technet上这篇教程进行操作，最终得到以下代码：

add-type -path "C:\oracle\product\12.1.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll"
$username = "XXXX"
$password = "XXXX"
$data_source = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX))(CONNECT_DATA = (SERVER=dedicated)(SERVICE_NAME=XXXX)))"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"

try{
    $con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)

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

很遗憾，我遇到了这个错误：

C:\Users\XXXX\Desktop\oracle_test.ps1 : Can’t open connection: User Id=XXXX;Password=XXXX;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX))(CONNECT_DATA = 
(SERVER=dedicated)(SERVICE_NAME=XXXX)))
System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "The type initializer for 'Oracle.ManagedDataAccess.Types.TimeStamp' threw an exception." ---> 
System.TypeInitializationException: The type initializer for 'Oracle.ManagedDataAccess.Types.TimeStamp' threw an exception. ---> System.Runtime.Serialization.SerializationException: Unable to find assembly 
'Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=XXXX'.
   at OracleInternal.Common.OracleTimeZone.GetInstance()
   at Oracle.ManagedDataAccess.Types.TimeStamp..cctor()
   --- End of inner exception stack trace ---
   at OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, String instanceName)
   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
   at CallSite.Target(Closure , CallSite , Object )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,oracle_test.ps1

我已经检查过连接字符串是正确的（对于tnsping也有效）
用户名和密码也是正确的
我用XXXX替换了任何不应该透露的内容
数据库版本为：Oracle Database 12c Release 12.1.0.1.0
32位和64位Powershell实例的错误相同
在第一次运行之前，Oracle.ManagedDataAccess.dll没有加载，在运行后它会一直加载，直到我关闭Shell

不幸的是，我既不是Oracle专家也不是Powershell专家（我更喜欢MySQL和Python），因此我非常感谢您可能有的任何想法和/或见解。

- Maurice

这个stackoverflow的答案有帮助吗？我不认为这是你代码的错误，可能是环境或前提条件的问题。 - G42

你的 Oracle 数据库版本是什么？ - Wernfried Domscheit

1

@WernfriedDomscheit 我已将其添加到列表中 - 12c Release 12.1.0.1.0 @gms0ulman 提到的 gacutil 在我的系统上似乎没有安装 - 我会进行调查。 - Maurice

错误提示表明出现了汇编问题。此时连接字符串甚至无法询问。是否已经加载了任何可能存在冲突的版本？您是从32位还是64位的shell运行此程序？您尝试过另一个吗？ - Matt

我尝试过这个方法 - 当我打开一个新的 shell 时，它没有被加载，在我执行我的脚本之后，它被加载并在后续的脚本运行中保持加载状态，直到我关闭该 shell。 - Maurice

显示剩余2条评论

5个回答

3

使用 server:port/service 语法。

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

- DrFloyd5

1

是的！Oracle服务器和服务必须在1522上监听，这是DBA的事情... 这里的服务条目可以只是一个简单的实例名称，比如ORA1... - Allen

1

这里是我用ODP.NET托管数据访问编写的代码（使用PowerShell，但您可以适应C#）... 我直接使用PowerShell Add-Type加载，但在C＃中将是using/reference...

使用连接生成器类创建连接，以确保生成的连接字符串完全符合要求。当然，您可以简化，但这样可以自我说明：

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;

}

获取一个连接：

$con = New - OracleConnection;

必要时打开/关闭（默认为打开状态...）

... ... 如果 ($con.State -ne [System.Data.ConnectionState]::Open) { $con.Open();# 没有我知道的参数... }

使用后关闭/处理...

    $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();

- Allen

0

这很可能是与Oracle 12.2.x有关的问题。
我不得不在数据库服务器上的sqlnet.ora文件中添加以下行，以允许来自旧版Oracle客户端的连接： SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

添加后，我可以使用Oracle 10g和11g客户端登录。

- Dennis

正如我之前提到的，老客户一再让我感到头疼，但我理解即使是规模较小的企业实体也必须在工作站配置方面保持一定的保守，不能轻易更新... - Allen

-1

我曾经遇到完全相同的问题，我将ODAC版本从12.2.0.1.0切换到11.2.0.3，成功地打开了连接并读取了数据。希望这可以帮助您。谢谢， SK

- user1783074

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Maurice · Accepted Answer

我不确定这是否严格来说是一个解决方案——我更倾向于将其归类为一种变通方法，但它对我有效。

经过更多的研究，我找到了一个适合替代Oracle.ManagedDataAccess.dll的方案。我发现了 .Net Framework 的 System.Data.OracleClient 类。(它需要已安装的 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() }
}