Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using Powershell 5.1 and Oracle.ManagedDataAccess.Client.OracleBulkCopy's WriteToServer I'm seeing silently missed inserts.

David Marti6 days ago — edited 6 days ago

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
}
This post has been answered by David Marti on Nov 7 2025
Jump to Answer
Comments
Post Details
Added 6 days ago
3 comments
38 views