Bulk-inserting into Oracle with OracleBulkCopy

The problem

Inserting 50,000+ rows one-by-one via parameterized INSERT is slow. Even with connection pooling, the round-trip overhead adds up. For a nightly ETL job or a bulk data migration this quickly becomes the bottleneck.

The pattern

using var bulkCopy = new OracleBulkCopy(connection)
{
    DestinationTableName = "TARGET_TABLE",
    BatchSize = 5000,
    BulkCopyTimeout = 120,
    NotifyAfter = 1000,
};

bulkCopy.ColumnMappings.Add("SourceCol", "DEST_COL");
// repeat for each column

bulkCopy.WriteToServer(dataTable);

Populate a DataTable first, then call WriteToServer. The driver batches the rows and sends them in chunks, which is dramatically faster than individual inserts.

Gotchas

Column mapping is case-sensitive on the Oracle side. Your destination column names must match exactly what’s in the schema (usually uppercase). bulkCopy.ColumnMappings.Add("id", "id") will silently map nothing if the column is actually ID.

DataTable column types matter. If you add a column as typeof(string) but the target is NUMBER, you’ll get a type mismatch at WriteToServer, not at column-mapping time. Map your DataTable columns to the correct CLR types upfront.

Transactions don’t work the way you expect. OracleBulkCopy doesn’t participate in ambient TransactionScope. If you need rollback on failure, wrap in an explicit OracleTransaction and pass it to the constructor: new OracleBulkCopy(connection, OracleBulkCopyOptions.Default, transaction).

Sequences and triggers. If your table uses a BEFORE INSERT trigger to populate a surrogate key from a sequence, OracleBulkCopy will fire the trigger per row — which is fine. But if your trigger does something expensive, you’ll see it at scale. Consider disabling non-essential triggers before a bulk load.

Benchmarks (rough)

On a table with ~20 columns and a single index:

Method	50k rows
Individual inserts	~4 min
OracleBulkCopy (batch 1000)	~12 sec
OracleBulkCopy (batch 5000)	~6 sec

Batch size of 5000 is a reasonable default. Going much higher doesn’t help much and increases memory pressure.

When not to use it

If you need per-row error handling (log bad rows, continue the rest), OracleBulkCopy isn’t it — it fails the whole batch on first error. In that case, use an array-bind insert with OracleCommand and ArrayBindCount instead.