Powershell 5.1 使用 Oracle Data Provider for .NET，查询结果不在表中

Question

我正在使用 SQLdeveloper 时尝试查询测试数据库

 从测试中选择 *

结果显示我的表格为

<前><代码> USER_ID 用户名 ---------- -------------------- 1001 用户1 1002 用户2

但是，当我尝试使用 Powershell ISE（即运行以下脚本的 PS 5.1）时

# This code is modified from https://purple.telstra.com.au/blog/using-powershell-to-query-oracle-dbs-without-using-the-oracle-client-oracle-data-provider-for-net
# ODAC downloaded form https://download.oracle.com/otn/other/ole-oo4o/ODP.NET_Managed_ODAC122cR1.zip?AuthParam=1645855877_652ef8e00f092e80aca26c6da393eb7d
# I've run install_odpm.bat and extracted the downlaoded files to C:\Oracle\odp.net


# Path to ODAC.NET Installation
Add-Type -Path "C:\Oracle\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$reader = $false
# SQL DB Username, Password and DataSource Alias (as per tnsnames.ora)
# I've grant demouser SYSDA permission
$username = "demouser"
$password = "demouser"
# Alias from TNSNames.ora
# Actually I find this in cmd to find XEPDB1
# lsnrctl status 
$datasource = "localhost/XEPDB1"

$connectionstring = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource 
$queryStatment = "SELECT * FROM TESTABLE"

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

    $cmd = $con.CreateCommand()
    $cmd.CommandType = "text"
    $cmd.CommandText = $queryStatment

    $reader = $cmd.ExecuteReader()
    $reader.Getname(0)
    #$reader.GetDataTypeName(0)
    while ($reader.Read()){
        $reader.GetDecimal(0)
        }
    $reader = $cmd.ExecuteReader()
    $reader.Getname(1)
    #$reader.GetDataTypeName(1)
    while ($reader.Read()){
        $reader.GetString(1);
        }
    $reader = $cmd.ExecuteReader()    
   }
catch {
    Write-Error (“Database Exception: {0}`n{1}” -f `
        $con.ConnectionString, $_.Exception.ToString())
} finally{
    if ($con.State -eq ‘Open’) { $con.close() }
}

，我只能将其作为对象获取，无法将其作为表格获取。

USER_ID
1001
1002
USERNAME
user1
user2

如何将结果作为表格获取？先感谢您。

对于 $reader = $cmd.ExecuteReader() 之后的 $reader。我得到了

FieldCount
----------
         2
         2

Answer 1

我认为使用DataTable切换更好

#region Get Oracle data

Add-Type -Path "$($PSScriptRoot)\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll" -ErrorAction Stop

$oCSBuilder = New-Object -TypeName 'Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder' -ErrorAction Stop
$oCSBuilder['user id'] = 'userid'
$oCSBuilder['password'] = 'xxx'
$oCSBuilder['Data Source'] = '(description=(address=(protocol=tcp)(host=x.x.x)(port=1521))(connect_data=(server=dedicated)(service_name=xxx)))'

# Create DataAdapter
$oDA = New-Object -TypeName 'Oracle.ManagedDataAccess.Client.OracleDataAdapter' -ArgumentList @(
    "select * from x.x where xx = 'xxx'",
    $oCSBuilder.ConnectionString
    ) -ErrorAction Stop

# Create DataTable
$oDT = New-Object -TypeName 'System.Data.DataTable' -ErrorAction Stop
# Fill DataTable
$oDA.Fill($oDT) | Out-Null
#Dispose dataAdapter
$oDA.Dispose()
　
$data = @($odt.Rows | Where-Object {$_.PROPERTY -eq 'Value'} | ....

Answer 2

找到一种简单的方法

$dataset = New-Object system.Data.DataSet 
$dataadapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($cmd)
[void]$dataadapter.fill($dataset) 
$dataset.Tables[0]

感谢@filimonic和https://docs.oracle。 com/database/121/ODPNT/OracleCommandBuilderClass.htm#ODPNT655