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

vijay9471
 
on Jun 26, 2021 05:36 AM
Sample_147000.zip
1197 Views

Hi Team,

I am facing issue for while inserting bulk data into oracle database from c# console app.

My data will be around 2 to 3 million records. I am getting this error invalid buffer length for ttc field. It was working fine in oracle 12 c. But when upgraded to 19.11 this issue is coming.

I followed below link of 1st approach. 

https://www.c-sharpcorner.com/article/two-ways-to-insert-bulk-data-into-oracle-database-using-c-sharp/ 

If i insert 1 lakh records using above link first approach it is working fine. So I decided to loop through records for every 1 lakh records.

Like after inserting one lakh records and it should pick another 1 lakh records from 100001 like that skip before 1 lakh records from DataTable.

I am loading all 2 million data in to DataTable initially.

Is this possible way by looping through DataTable?

Please suggest me with the solution for this problem.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 27, 2021 10:57 PM
on Jun 28, 2021 11:11 PM

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