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

Getting ready

To run the code, irst execute EMPLOYEE_PACKAGE.pls in a DBMS tool. This sample package
SQL ile is included with the code for this recipe.

How to do it...

1. Create a function to return a connection object:

function New-Connection
{
$dataSource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))"
$connectionString = ("Data Source={0};User
Id=HR;Password=pass;Connection Timeout=10" -f $dataSource)
New-Object Oracle.DataAccess.Client.OracleConnection($connecti
onString)
}
Create a function to set up a command for executing a database
procedure:
function New-ProcCommand ($procedure, $parameters)
{
$cmd = New-Object Oracle.DataAccess.Client.
OracleCommand($procedure, (New-Connection))
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$parameters | foreach {$cmd.Parameters.Add($_) | Out-Null}
$cmd
}

2. Create helper functions for creating OracleParameter objects:

function New-Param ($name, $type, $value,
$size = 0, $direction = [System.Data.
ParameterDirection]::Input)
{
New-Object Oracle.DataAccess.Client.OracleParameter($name,
$type, $size) `
-property @{Direction = $direction; Value = $value}
}

function New-CursorParam ($name)
{
New-Param -name $name -type ([Oracle.DataAccess.Client.
OracleDbType]::RefCursor) `
-direction ([System.Data.ParameterDirection]::Output)
}