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

3. Add a function to return a data reader for reading procedure Ref Cursors:

function Get-ProcReader ($procedure, $parameters)
{
$cmd = New-ProcCommand $procedure $parameters
if ($cmd.Connnection.State -ne [System.Data.
ConnectionState]::Open)
{
$cmd.Connection.Open()
}
,$cmd.ExecuteReader()
}

4. Deine a function to invoke a LOAD_EMPLOYEE procedure from EMPLOYEE_PACKAGE
and load the results into a DataSet .
function Get-EmployeeDataSet ($employeeId)
{
$procedure = "HR.EMPLOYEE_PACKAGE.LOAD_EMPLOYEE"
$params = @(
(New-Param -name "I_EMPLOYEE_ID" -type ([Oracle.
DataAccess.Client.OracleDbType]::Int32) -value $employeeId)
(New-CursorParam -name "O_EMPLOYEES")
(New-CursorParam -name "O_LOCATIONS")
)
$ds = New-Object System.Data.DataSet("EmployeesDataSet")
$empDT = $ds.Tables.Add("EMPLOYEES")
$locDT = $ds.Tables.Add("LOCATIONS")
$reader = Get-ProcReader $procedure $params
$empDT.Load($reader)
$locDT.Load($reader)
$reader.Close(); $reader.Dispose()
$ds
}

5. Add code to invoke the function and output the rows returned:

$ds = Get-EmployeeDataSet -employeeId 203
$ds.Tables["EMPLOYEES"]
$ds.Tables["LOCATIONS"]
$ds.Dispose()

6. Run the script. Sample output follows.

EMPLOYEE_ID : 203
FIRST_NAME : Susan
LAST_NAME : Mavris
EMAIL : SMAVRIS
PHONE_NUMBER : 515.123.7777