# How iterate through dataSet from Oracle SQL Query

**URL:** <https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348>\
**Category:** PowerShell Help\
**Created:** [January 13, 2022, 7:04pm UTC](https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348 "2022-01-13T19:04:48Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![clearlymichele](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/clearlymichele/32/2000_2.png) [@clearlymichele](https://forums.powershell.org/u/clearlymichele)\
**Post date:** [January 13, 2022, 7:04pm UTC](https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348/1 "2022-01-13T19:04:48Z")

</div>

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.

```auto
    $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:

```auto
> 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](https://key2consulting.com/powershell-sql-iterate-rows-of-data-returned-from-sql-stored-procedure/), 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?

---

<div class="post-metadata">

**Author:** ![tonyd](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/tonyd/32/1041_2.png) [@tonyd](https://forums.powershell.org/u/tonyd)\
**Post date:** [January 13, 2022, 7:15pm UTC](https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348/2 "2022-01-13T19:15:20Z")

</div>

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:

```auto
[System.String]$dr

```

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

---

<div class="post-metadata">

**Author:** ![clearlymichele](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/clearlymichele/32/2000_2.png) [@clearlymichele](https://forums.powershell.org/u/clearlymichele)\
**Post date:** [January 13, 2022, 7:19pm UTC](https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348/3 "2022-01-13T19:19:49Z")

</div>

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

```auto
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
}

```

---

<div class="post-metadata">

**Author:** ![tonyd](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/tonyd/32/1041_2.png) [@tonyd](https://forums.powershell.org/u/tonyd)\
**Post date:** [January 13, 2022, 11:19pm UTC](https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348/4 "2022-01-13T23:19:26Z")

</div>

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

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

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

```

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:19pm UTC](https://forums.powershell.org/t/how-iterate-through-dataset-from-oracle-sql-query/18348/5 "2024-05-16T20:19:51Z")

</div>