Query Oracle Database using Powershell

Question

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.

Answer 1

Managed to find the data in

$SelectDataTable[0].Rows

Answer 2

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

Answer 3

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])"}
    }
}