Instant Oracle Database and PowerShell How-to - Instant Oracle Database and PowerShell How-to - page 13

}
Write-Output $cityState
}

In this example each DataRow that was unrolled from the DataTable is piped to the
ForEach-Object cmdlet ( foreach alias) one at a time. Note that all the data is
still loaded into memory inside the DataTable in one database call; this just iterates
over them in a "lazy fashion".

For more information on the differences between the foreach statement
used in the irst example with the ForEach-Object cmdlet used
here, see: http://poshoholic.com/2007/08/21/essential-
powershell-understanding-foreach/ .

The other item of note in this script is the State/Province ield is checked to ensure it
is not equal to null, using [DBNull]::Value and not $null .

Using a DataReader
At times it may be beneicial to read one record at a time from Oracle, usually when iterating
through a large number of records.

1. We can do this using a function similar to Get-DataTable but returning a

DataReader instead:
function Get-DataReader
{
Param(
[Parameter(Mandatory=$true)]
[Oracle.DataAccess.Client.OracleConnection]$conn,
[Parameter(Mandatory=$true)]
[string]$sql
)
$cmd = New-Object Oracle.DataAccess.Client.
OracleCommand($sql,$conn)
$reader = $cmd.ExecuteReader()
return ,$reader
}

2. We invoke the function in a similar fashion:

$sql = "select city, state_province from locations order by city,
state_province"
$reader = Get-DataReader $conn $sql

The iteration changes to use $reader.Read() and string ields are read using the
DataReader 's GetString method, combined with GetOrdinal to get the position