Query Oracle Database using Powershell

Christopher Jack 1,611 Reputation points
2021-04-09T08:06:10.253+00:00

Hi,

I have the oracle client installed on my machine and I am using the following code to query the oracle database

### try to load assembly, fail otherwise ###
$Assembly = [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

if ( $Assembly ) {
    Write-Host "System.Data.OracleClient Loaded!"
}
else {
    Write-Host "System.Data.OracleClient could not be loaded! Exiting..."
    Exit 1
}
### connection string ###
$OracleConnectionString = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=$servicename)));uid=user;pwd=password;"

### open up oracle connection to database ###
$OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString);
$OracleConnection.Open()

try {

    ### sql query command ###
    $OracleSQLQuery = "SELECT * FROM charge_code"

    ### create object ###
    $SelectCommand1 = New-Object System.Data.OracleClient.OracleCommand;
    $SelectCommand1.Connection = $OracleConnection
    $SelectCommand1.CommandText = $OracleSQLQuery
    $SelectCommand1.CommandType = [System.Data.CommandType]::Text

    ### create datatable and load results into datatable ###
    $SelectDataTable = New-Object System.Data.DataTable
    $SelectDataTable.Load($SelectCommand1.ExecuteReader())

}
catch {

    Write-Host "Error while retrieving data!"

}

It is retrieving the data and putting it in $SelectDataTable

However I ma not sure how to access the data .. there is

$SelectDataTable.Colums[0] which for example contains the column header.

However... I cant seem to locate where the data is stored in the object?

Any help appreciated.

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,360 questions
0 comments No comments
{count} votes

2 additional answers

Sort by: Most helpful
  1. Reji Kunjappan 6 Reputation points
    2022-10-31T15:15:53.967+00:00

    $SelectDataTable | select -Property * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors |Out-GridView

    1 person found this answer helpful.
    0 comments No comments

  2. Per Ekstedt 0 Reputation points
    2023-08-16T16:44:38.2066667+00:00

    Very helpful, including the answers...

    Please receive my generic extensions to catch the result, extend the code in the example above with this :

    if ($SelectDataTable[0].Rows.Count -ge 5) {
        $SelectDataTable | 
    select -Property * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors |Out-GridView
    }
    else {
        $i = 0
        $e = $SelectDataTable[0].Rows.GetEnumerator()
        While ($e.Movenext()) {
            write-host ""
            $i = $i + 1
            write-host "Row: $($i)"
            $SelectDataTable[0].Columns | 
    foreach {write-host "value for $($_.ColumnName) is : $($e.current[$_.ColumnName])"}
        }
    }
    
    0 comments No comments