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

In addition to inserts, updates, and deletes, Invoke-Oracle also works ine for simple DDL
operations such as CREATE TABLE or DCL operations such as GRANT or REVOKE :
Invoke-Oracle $conn "ALTER TABLE HR.DEPARTMENTS ADD NOTE VARCHAR2(100)
NULL"
Invoke-Oracle $conn "ALTER TABLE HR.DEPARTMENTS DROP COLUMN NOTE"
Updating with a DataAdapter
Sometimes you may want to select data into a DataTable using an OracleDataAdapter as
in the recipe Retrieving data (Simple) , make some changes in PowerShell, then update those
back to the Oracle table without manually creating an UPDATE statement and command:
$sql = "SELECT EMPLOYEE_ID, MANAGER_ID, SALARY FROM HR.EMPLOYEES WHERE
DEPARTMENT_ID = 90"
$cmd = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd)
$cmdBuilder = new-object Oracle.DataAccess.Client.OracleCommandBuilder
$da
$dt = New-Object System.Data.DataTable
[void]$da.Fill($dt)
foreach ($dr in $dt.Rows) {
if ($dr.manager_id -eq 100) {$dr.salary += $dr.salary * .10}
}
"Updated {0} records" -f $da.Update($dt)
An OracleCommandBuilder is created, and it is passed the data adapter that it will use to
automatically create an UpdateCommand to be used when the Update method is called on
the OracleDataAdapter to persist the changes. The command builder will use metadata
from the DataTable and SelectCommand ( $cmd ) to construct the update command. It will
listen for changes to the data table to track what changed.
This method only works for single-table updates and you may ind that it generates SQL that
you might not expect. You can inspect $cmdBuilder.GetUpdateCommand() to see the
update statement it generates.
Executing database procedures (Medium)
In this recipe we will explore executing a database package procedure that returns multiple
Ref Cursors and loading that data into a DataSet .