Database Reference
In-Depth Information
After connecting and inserting the test region using
Add-Oracle
from the previous recipe,
an
OracleBulkCopy
object is created and passed the connection.
OracleBulkCopy
is much more eficient at bulk loading data from a data source into an Oracle table, than
iterating through data and inserting records one at a time. The
DestinationTableName
is set to the
COUNTRIES
table, a
BulkCopyTimeout
of 5 minutes is set (
300 seconds
),
and we ask to be notiied after every 50 records are processed with
NotifyAfter
. Next, the
column mappings are set with
ColumnMappings.Add
calls, where the irst argument is the
source column name in the
DataTable
and the second is the destination column name in
the Oracle table. The column mappings are required when there are any differences between
column names, ordinal positions, or column counts between the source and destination. Note
that
REGION_ID
has to be mapped even though the name matches and
[void]
prevents
outputting the mapping object created.
Next, an event listener is registered for the
OracleRowsCopied
event of the
OracleBulkCopy
object in order to output progress information during the bulk import.
In this sample there are only a couple hundred rows in the source XML ile and they are
processed so quickly that the progress bar is not really visible, short of setting a breakpoint.
You will often be inserting considerably more records and the progress information may
be more useful. The code in the
-Action
block deines the event logic of referencing the
number of rows copied so far, the total row count, and the completion percentage, and writing
that to the host output and progress. Note that
$countryDT.Rows.Count
is passed as
the
-MessageData
parameter, as variables outside the
Action
block cannot be reliably
accessed. This may work from the PowerShell ISE but not from the PowerShell console.
Finally the
WriteToServer
method invokes the bulk insert operation. We pass it the
countries'
DataTable
but it can take different data sources including an array of
DataRow
,
a
DataReader
or an
OracleRefCursor
type. A
StopWatch
object is used to calculate the
duration and afterwards we clean up by unregistering the event, closing the connection, and
disposing objects.
OracleBulkCopy
will not perform updates, only inserts. In this example
the
countries
XML ile does not contain any countries already in the
COUNTRIES
database table. If it did, constraints could be violated or duplicate
data inserted. Additionally if the country names in the source data exceeded
the column size in the database, an ORA-26093 error would be raised.
There's more...
In some cases with a limited amount of records to insert, iterating over data in a ile and
inserting records one at a time may be acceptable performance wise for the lexibility of
custom processing.
For very large data sets you may need to look at automating SQL *Loader from PowerShell
or using Oracle's External Tables feature.
Search WWH ::
Custom Search