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?