How iterate through dataSet from Oracle SQL Query

I have a powershell script that returns a dataset. I’m trying to figure out how to iterate through the dataset and get a hold of the resulting rows. This query returns results In Oracle SQL Developer, and Powershell prints that it returned 2784 records.

This is my code so far.

    $connectionString = "Data Source=$dataSource;User Id=$username;Password=$password;" 
    $con = New-Object System.Data.OracleClient.OracleConnection($connectionString) 
    $query =  Get-Content $sqlPath 
    Write-Host "query: $query"
    $con.open()
    $dtSet = New-Object System.Data.DataSet
    $OracleAdapter = New-Object System.Data.OracleClient.OracleDataAdapter($query,$con)
    #[void]$OracleAdapter.Fill($dtSet)
    $recordCount = $OracleAdapter.Fill($dtSet)
    Write-Host "Count: $recordCount" -ForegroundColor Cyan #prints 2784
    foreach ($dr in $dtSet)
    {
        Write-Host($dr)  #prints System.Data.DataSet, but I want to print FirstOne, or FirstOne   Current  Apps   etc.
    }
    $data = $dataSet.Tables[1]  #this has error, cannot index into a null array. Gives same error with Tables[0]

In Oracle Sql Developer, the query results look like this:

> DeviceType  Version  CompTypeName  ComponentName        CompStateCode          SeverityName
> FirstOne    Current  Apps          Company Monitoring   Archive.OutOfMaterial  Normal
> SecondOne   Current  Apps          Application Sec      Archive.Sess           Warning

I was looking at iterate through data, but they use

$data = $dataSet.Tables[0]

and that gives me the error message in my code comment.

So my question is, how to I iterate over the returned dataset rows, and print the contents? Also, how do I handle individual row/column info so I can look up and re-assign the info to variables or print/handle individually? And finally, is there a way to print the full data returned to see it’s actually there, and not 2784 empty records?

For your Index error, I dont see that you have defined $dataSet, should that be $dtSet?

Also, what happens if you prefix $dr as such:

[System.String]$dr

I have no way to test … no Oracle DB’s here …

Thank you! There was a typo that I corrected for $data = $dtSet.Tables[0]

I tried

foreach ([System.String]$dr in $dtSet)

and it has an error “missing variable name after foreach”, and unexpected token in…

I’m trying

foreach ($dr in $data)
{
     Write-Host($dr)  #prints System.Data.DataRow
     Write-Host($dr.ItemArray)  #prints FirstOne Current  Apps  Company Monitoring Archive.OutOfMaterial  Normal
     Write-Host($dr.ItemArray[0])  #prints FirstOne 
     Write-Host($dr.ItemArray['SEVERITYNAME']) #prints nothing
}

So sorry, I should have been more clear. This is what I was thinking:

{
        Write-Host([System.String]$dr)  #prints System.Data.DataSet, but I want to print FirstOne, or FirstOne   Current  Apps   etc.
    }