与 Oracle 数据库的 Powershell 连接

Powershell Connection to Oracle Database

提问by Maurice

I'm having trouble connecting to an Oracle database from Powershell using the Oracle.ManagedDataAccess.dll.

我在使用Oracle.ManagedDataAccess.dll.

I followed thistutorial on Technet and ended up with this code:

我在 Technet 上遵循了本教程，最终得到了以下代码：

add-type -path "C:\oracle\product.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() }
}

Unfortunately I'm getting this error:

不幸的是，我收到此错误：

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

• I have checked, that the connection string is correct (it works for tnsping)
• The Username and password are correct as well
• I replaced anything that shouldn't be disclosed with XXXX
• The database version is: Oracle Database 12c Release 12.1.0.1.0
• The error is the same for the 32-Bit and 64-Bit Powershell instances
• Before the first run the Oracle.ManagedDataAccess.dllisn't loaded, after the run it stays loaded until I close that shell

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

Unfortunately I'm neither an Oracle nor a Powershell Expert (I prefer MySQL and Python), therefore I would really appreciate any ideas and/or insights you might have.

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

回答by Maurice

Im not sure if this is technically a solution - I'd classify it more as a workaround, but it worked for me.

我不确定这在技术上是否是一个解决方案 - 我将它更多地归类为一种解决方法，但它对我有用。

---

---

After doing some more research I found a suitable alternative to the Oracle.ManagedDataAccess.dll. I found the System.Data.OracleClientclass of the .Net Framework. (It requires an installed Oracle Client, which the machine fortunately has)

在做了更多的研究之后，我找到了一个合适的替代Oracle.ManagedDataAccess.dll. 我找到了.Net Framework的System.Data.OracleClient类。（它需要一个已安装的 Oracle 客户端，幸运的是机器有）

Here's an outline of the solution that worked for me:

以下是对我有用的解决方案的概述：

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() }
}

回答by DrFloyd5

Use the server:port/service syntax.

使用 server:port/service 语法。

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

回答by user1783074

I had the exact same issue and I switched from ODAC version 12.2.0.1.0 to version 11.2.0.3 and it worked like a charm to Open connection and read data. Hope this helps. Thanks, SK

我遇到了完全相同的问题，我从 ODAC 版本 12.2.0.1.0 切换到版本 11.2.0.3，它就像打开连接和读取数据的魅力一样。希望这可以帮助。谢谢，SK

回答by Dennis

It could very well be an issue with oracle 12.2.x
I had to add the following lines to the sqlnet.ora file on the database server to allow connections from older oracle clients: SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

这很可能是 oracle 12.2.x 的问题
我必须将以下几行添加到数据库服务器上的 sqlnet.ora 文件中以允许来自旧版 oracle 客户端的连接： SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8 SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Once added I could login with oracle 10g and 11g clients

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