[Solved] Bulk upload large data into oracle database Error: invalid buffer length for ttc field

Hi vijay9471,

Split the DataTable to multiple, then loop through the DataTable to bulk insert.

Namespaces

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

static void Main(string[] args)
{
    List<DataTable> tables = SplitTable(GetData(), 100000);
    // Using loop insert the record.
}

private static DataTable GetData()
{
    DataTable dt = new DataTable();
    // Get data from database.

    return dt;
}

private static List<DataTable> SplitTable(DataTable originalTable, int batchSize)
{
    List<DataTable> dts = new List<DataTable>();
    DataTable dt = new DataTable();
    dt = originalTable.Clone();
    int j = 0;
    int k = 1;
    if (originalTable.Rows.Count <= batchSize)
    {
        dt.TableName = "Table_" + k;
        dt = originalTable.Copy();
        dts.Add(dt.Copy());
    }
    else
    {
        for (int i = 0; i < originalTable.Rows.Count; i++)
        {
            dt.NewRow();
            dt.ImportRow(originalTable.Rows[i]);
            if ((i + 1) == originalTable.Rows.Count)
            {
                dt.TableName = "Table_" + k;
                dts.Add(dt.Copy());
                dt.Rows.Clear();
                k++;
            }
            else if (++j == batchSize)
            {
                dt.TableName = "Table_" + k;
                dts.Add(dt.Copy());
                dt.Rows.Clear();
                k++;
                j = 0;
            }
        }
    }

    return dts;
}

VB.Net

Sub Main(args As String())
    Dim tables As List(Of DataTable) = SplitTable(GetData(), 100000)
    ' Using loop insert the record.
End Sub

Private Function GetData() As DataTable
    Dim dt As DataTable = New DataTable()
    ' Get data from database.

    Return dt
End Function

Private Function SplitTable(originalTable As DataTable, batchSize As Integer) As List(Of DataTable)
    Dim dts As List(Of DataTable) = New List(Of DataTable)()
    Dim dt As DataTable = New DataTable()
    dt = originalTable.Clone()
    Dim j As Integer = 0
    Dim k As Integer = 1
    If originalTable.Rows.Count <= batchSize Then
        dt.TableName = "Table_" & k
        dt = originalTable.Copy()
        dts.Add(dt.Copy())
    Else
        For i As Integer = 0 To originalTable.Rows.Count - 1
            dt.NewRow()
            dt.ImportRow(originalTable.Rows(i))
            If (i + 1) = originalTable.Rows.Count Then
                dt.TableName = "Table_" & k
                dts.Add(dt.Copy())
                dt.Rows.Clear()
                k += 1
            ElseIf Threading.Interlocked.Increment(j) = batchSize Then
                dt.TableName = "Table_" & k
                dts.Add(dt.Copy())
                dt.Rows.Clear()
                k += 1
                j = 0
            End If
        Next
    End If

    Return dts
End Function