Using Oracle.ManagedDataAccess.dll 23.26 from Powershell 5.1 on Windows 2019 Server.
I have scripts that copy various Oracle audit tables to a central Oracle repository database.
The tables being copied tend to have an Oracle CLOB column containing DDL commands.
I am using Oracle.ManagedDataAccess.Client.OracleBulkCopy's WriteToServer method to get good throughput.
What I'm noticing is that out of thousands or tens of thousands of records copied, typically a very specific number 14 will be silently skipped.
There's nothing special about the records skipped versus the ones that are correctly inserted.
Any ideas? Changing from passing a DataReader to a Datatable doesn't help.
So I think the problem is that OracleBulkCopy silently skips over records.
I do not see any exceptions or logged errors. Here is some partial sample Powershell code.
Are there known issues with using Powershell that wouldn't exist if I were to convert to C#?
# Bulk write via streaming records from a reader.
function Write-DataReaderToOracleBulk {
param (
[System.Data.IDataReader]$DataReader,
[Oracle.ManagedDataAccess.Client.OracleConnection]$destConn,
[string]$TargetTableName,
[hashtable]$ColumnMappings = $null
)
$ParsedResult = Parse-OracleIdentifier $TargetTableName
if ($destConn.State -ne 'Open') {
$destConn.Open()
}
# Create a [ref] variable to hold the result
$retVal = [ref] 0
try {
#$bulkCopyOptions = [Oracle.ManagedDataAccess.Client.OracleBulkCopyOptions]::UseInternalTransaction
#$bulkCopy = New-Object Oracle.ManagedDataAccess.Client.OracleBulkCopy($destConn, $bulkCopyOptions)
$bulkCopy = New-Object Oracle.ManagedDataAccess.Client.OracleBulkCopy($destConn)
$bulkCopy.BatchSize = 50000
$bulkCopy.BulkCopyTimeout = 3600 # 1 Hour
$bulkCopy.DestinationSchemaName = $ParsedResult.Schema
$bulkCopy.DestinationTableName = $ParsedResult.Table
$bulkCopy.NotifyAfter = 1
# Define the event handler
$handler = [Oracle.ManagedDataAccess.Client.OracleRowsCopiedEventHandler]{
param($sender, $eventArgs)
$retVal.Value = $eventArgs.RowsCopied
}
# Attach the handler
$bulkCopy.add_OracleRowsCopied($handler)
if ($ColumnMappings) {
foreach ($sourceCol in $ColumnMappings.Keys) {
[void]$bulkCopy.ColumnMappings.Add($sourceCol, $ColumnMappings[$sourceCol])
}
}
[void]$bulkCopy.WriteToServer($DataReader)
$result = [int]$retVal.Value
} catch {
Append-Log -Text ("{0:u} Write-DataReaderToOracleBulk Bulk copy failed: $($_.Exception.Message) `n" -f (Get-Date))
}
finally {
if ($handler) { $bulkCopy.remove_OracleRowsCopied($handler) }
if ($bulkCopy -ne $null) {
try { $bulkCopy.Close() } catch {}
try { $bulkCopy.Dispose() } catch {}
$bulkCopy = $null
}
}
return $result
}