Database Reference
In-Depth Information
Another popular technique is using array binding, which we will look at next. You may need
to experiment with different techniques and compare performance and trade-offs.
Using array binding for bulk inserts
Another common option for eficient bulk inserts is using array binding to insert records in
one batch operation based on arrays of values:
$regions = Import-CSV (join-path (Get-ScriptDirectory) Regions.csv)
$regionIds = New-Object int[] $regions.Count
$regionNames = New-Object string[] $regions.Count
$index = 0
foreach ($r in $regions) {
$regionIds[$index] = $r.RegionId; $regionNames[$index++] =
$r.RegionName
}
$cmd = New-Object Oracle.DataAccess.Client.OracleCommand( `
"insert into regions values (:region_id, :region_name)",$conn)
$idParam = $cmd.Parameters.Add(":region_id", [Oracle.DataAccess.
Client.OracleDbType]::Int32)
$nameParam = $cmd.Parameters.Add(":region_name", [Oracle.DataAccess.
Client.OracleDbType]::Varchar2)
$idParam.Value = $regionIds; $nameParam.Value = $regionNames
$cmd.ArrayBindCount = $regions.Count
$trans = $conn.BeginTransaction()
$cmd.ExecuteNonQuery()
$trans.Commit()
The sample code for this recipe includes a simple
Regions.csv
ile with region ID and region
name. This ile is read with
Import-CSV
and empty
int
and
string
arrays are created with
a size matching the record count. Next, the arrays are illed by looping over the loaded CSV
data. Bind variable parameters are used as before in the recipe,
Adding Records
but this
time the values of the parameters are set to the arrays instead of single values. The other key
here is setting the
ArrayBindCount
to the number of records. Additionally, a transaction is
created to ensure all records are inserted or rolled back.
Updating and deleting records (Simple)
In this recipe we'll look at invoking SQL commands to make changes to Oracle, mostly with
UPDATE
and
DELETE
statements but we will also look at related topics such as modifying an
Oracle schema.
Search WWH ::
Custom Search