PowerShell中如何从OracleConnection逐行读取数据到本地DataTable？

问题背景 #

现有结构一致的Oracle源库与SQL Server目标库，共80张表。79张小表可直接用DataTable.Load()全量加载后通过SqlBulkCopy写入；但1张含十几MB XML CLOB的超大表需逐行读取、处理后写入，此前尝试DataTable.LoadDataRow()未满足需求。

核心解决方案：用OracleDataReader流式逐行读取，批量写入 #

通过OracleDataReader流式读取单条记录，单独处理CLOB字段后存入DataTable，达到指定批量大小后写入目标库，避免内存溢出。

步骤1：建立OracleDataReader并初始化匹配结构的DataTable #

# 假设已完成Oracle连接初始化 $oracleConn
$targetTableName = "你的超大表名"
$commandText = "SELECT * FROM $targetTableName"
$oracleCmd = New-Object System.Data.OracleClient.OracleCommand($commandText, $oracleConn)
$reader = $oracleCmd.ExecuteReader()

# 创建与源表结构匹配的DataTable
$dt = New-Object System.Data.DataTable
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
    $colName = $reader.GetName($i)
    $colType = $reader.GetFieldType($i)
    # 针对XML CLOB列调整类型（适配SQL Server的XML/VARCHAR(MAX)）
    if ($colName -eq "XML_CLOB列名") {
        $dt.Columns.Add($colName, [string])
    } else {
        $dt.Columns.Add($colName, $colType)
    }
}

步骤2：逐行读取并批量写入 #

$batchSize = 10 # 根据单条记录大小调整，避免内存占用过高
$rowCounter = 0

# 初始化SqlBulkCopy（假设已完成SQL Server连接 $sqlConn）
$sqlBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
$sqlBulkCopy.DestinationTableName = $targetTableName
$sqlBulkCopy.BatchSize = $batchSize

while ($reader.Read()) {
    $newRow = $dt.NewRow()
    for ($i = 0; $i -lt $reader.FieldCount; $i++) {
        $colName = $reader.GetName($i)
        if ($colName -eq "XML_CLOB列名") {
            # 读取Oracle CLOB完整内容
            $clob = $reader.GetOracleClob($i)
            $newRow[$colName] = $clob.ReadToEnd()
            $clob.Close() # 释放资源
        } else {
            $newRow[$colName] = $reader.GetValue($i)
        }
    }

    # 执行自定义数据处理逻辑
    # 示例：$newRow["修改列名"] = $newRow["原列名"].ToString().Trim()

    $dt.Rows.Add($newRow)
    $rowCounter++

    # 达到批量阈值时写入目标库
    if ($rowCounter -ge $batchSize) {
        $sqlBulkCopy.WriteToServer($dt)
        $dt.Clear()
        $rowCounter = 0
    }
}

# 处理剩余未批量的记录
if ($rowCounter -gt 0) {
    $sqlBulkCopy.WriteToServer($dt)
    $dt.Clear()
}

# 清理资源
$reader.Close()
$oracleCmd.Dispose()
$sqlBulkCopy.Dispose()

关键说明 #

• 为何不用LoadDataRow()：该方法依赖字段值数组直接赋值，Oracle CLOB字段无法通过普通GetValue()获取完整内容，需单独用GetOracleClob()读取，流式处理更可靠。
• 批量大小调整：单条记录10MB时，建议将$batchSize设为10-20，避免内存占用过高。
• 资源释放：必须手动关闭CLOB对象、DataReader及命令对象，防止Oracle连接泄漏。

内容的提问来源于stack exchange，提问作者Piotr L