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

4. Iterate through the data and perform some processing:

foreach ($dr in $dt.Rows)
{
$eligible = [DateTime]::Now.AddYears(-5) -ge $dr.hire_date
if ($eligible)
{
Write-Output ("{0} {1} is eligible" -f $dr.first_name,
$dr.last_name)
}
}

5. When inished, close the connection:
$conn.Close()

6. Run the script. The output will vary by date but should be similar to the following:

Retrieved 5 records:

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- ---------- --------- ---------
145 John Russell 10/1/2004 12:00:00 AM
146 Karen Partners 1/5/2005 12:00:00 AM
147 Alberto Errazuriz 3/10/2005 12:00:00 AM
148 Gerald Cambrault 10/15/2007 12:00:00 AM
149 Eleni Zlotkey 1/29/2008 12:00:00 AM

John Russell is eligible
Karen Partners is eligible
Alberto Errazuriz is eligible

How it works...

Get-DataTable irst deines the connection and SQL parameters more formally within a
Param block and uses a Parameter attribute before each to specify additional metadata
indicating that the parameters are required (preferred over using throw ).

Next, an OracleCommand object is created using the connection and SQL string parameters
passed in. That command is passed in as the selectCommand argument when constructing
the OracleDataAdapter on the next line. A new DataTable object is then created and it
is populated using the data adapter with [void]$da.Fill($dt) ; void prevents the Fill
output so the function only has a single value being returned. The adapter can also ill a
DataSet when multiple tables are involved.