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

There's more...
In this example we executed a package procedure that returned Ref Cursors but we could use
the same or similar code to invoke functions or procedures inside or outside a package that
perform other tasks such as inserting data or returning simple values. The sample code for
this recipe includes other script iles with these examples. Let's look at invoking a function.
Invoking a function
ODP.NET uses positional parameter binding by default. When calling a function we want
to set $cmd.BindByName = $true for the command (in New-ProcCommand ). Afterwards
the following code is used to invoke the GET_EMPLOYEE_MANAGER function in the
EMPLOYEE_PACKAGE to return the manager name of a given employee ID:
function Invoke-Proc ($procedure, $parameters)
{
$cmd = New-ProcCommand $procedure $parameters
if ($cmd.Connnection.State -ne [System.Data.
ConnectionState]::Open) {$cmd.Connection.Open()}
$cmd.ExecuteNonQuery() | Out-Null
$cmd.Connection.Close(); $cmd.Connection.Dispose(); $cmd.Dispose()
}
function Get-EmployeeManager ($employeeId)
{
$params = @(
(New-Param -name "i_employee_id" -type ([Oracle.DataAccess.
Client.OracleDbType]::Int32) -value $employeeId)
(New-Param -name "RETURN_VALUE" -type ([Oracle.DataAccess.
Client.OracleDbType]::Varchar2) `
-direction ([System.Data.ParameterDirection]::ReturnValue
) -size 46)
)
Invoke-Proc "HR.EMPLOYEE_PACKAGE.get_employee_manager" $params
$params[1].Value
}
"Manager of employee id 200 is {0}" -f (Get-EmployeeManager 200)
This produces the following output:
Manager of employee id 200 is Neena Kochhar
Alternatively, without using BindByName , CommandType.Text could be used instead of
CommandType.StoredProcedure and the function could be called inside a BEGIN/END
PL/SQL block.