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

How to do it...

1. Deine the function Invoke-Oracle :
function Invoke-Oracle
{
Param(
[Parameter(Mandatory=$true)][Oracle.DataAccess.Client.
OracleConnection]$conn,
[Parameter(Mandatory=$true)][string]$sql,
[Parameter(Mandatory=$false)][System.Collections.
Hashtable]$paramValues,
[Parameter(Mandatory=$false)][switch]$passThru
)
$cmd = New-Object Oracle.DataAccess.Client.
OracleCommand($sql,$conn)
$cmd.BindByName = $true
if ($paramValues)
{
foreach ($p in $paramValues.GetEnumerator())
{
$oraParam = New-Object Oracle.DataAccess.Client.
OracleParameter
$oraParam.ParameterName = $p.Key
$oraParam.Value = $p.Value
$cmd.Parameters.Add($oraParam) | Out-Null
}
}
$result = $cmd.ExecuteNonQuery()
$cmd.Dispose()
if ($passThru) { $result }
}

2. Load ODP.NET and connect to Oracle as in previous recipes.

3. Make some calls to Add-Oracle with various INSERT , UPDATE , and DELETE

SQL statements:
$affected = Invoke-Oracle $conn -PassThru `
"UPDATE HR.JOBS SET MIN_SALARY = MIN_SALARY + 1000 WHERE
UPPER(JOB_TITLE) LIKE '%MANAGER%'"
"Updated {0} job record(s)" -f $affected

$cities = "Jacksonville", "Orlando", "Tampa", "Miami", "Del Ray