Earlier this year, someone from this forum helped me a lot to create a routine to do BULK INSERTS to an Oracle table using VB.NET.
This was the routine (with slight modifications specific to my needs):
Private Function BulkInsertsToTable(ByVal maxArrayBindCount As Integer, ByVal TableNameforSQL As String, ByRef dTable As DataTable) As Integer
Dim rtn_insert_count As Integer = 0
Dim MAX_ARRAYBINDCOUNT As Integer = maxArrayBindCount
Dim SQLStatement = "Select from " & TableNameforSQL*
*If ConnectedToDatabase() Then*
*Dim mCommand = New OracleCommand(SQLStatement)*
*Using mCommand*
*mCommand.Connection = mConnection*
*Dim da As New OracleDataAdapter(mCommand)*
*Dim cb As New OracleCommandBuilder(da)*
*Dim theInsertCommand As OracleCommand = cb.GetInsertCommand()*
*' build the generic storage for the field values based upon the insert command*
*' COLUMNS x ROWS*
*Dim holder As Object()() = New Object(theInsertCommand.Parameters.Count - 1)() {}*
*For x As Integer = 0 To theInsertCommand.Parameters.Count - 1*
*holder(x) = New Object(MAX_ARRAYBINDCOUNT - 1) {}*
*Next*
*Dim total_reads As Integer = 0*
*Dim current_reads As Integer = 0*
*' the row counter*
*For Each d As DataRow In dTable.Rows*
*total_reads = total_reads + 1*
*' populate the column array for this row*
*For c As Integer = 0 To theInsertCommand.Parameters.Count - 1*
*holder(c)(current_reads) = d(c)*
*Next*
*current_reads = current_reads + 1*
*If current_reads = MAX_ARRAYBINDCOUNT Then*
*Dim num_inserted As Integer = ArrayBoundInsertHelper(theInsertCommand, holder, current_reads)*
*rtn_insert_count = rtn_insert_count + num_inserted*
*current_reads = 0*
*End If*
*Next*
*'*
*' if anything left we insert them now*
*'*
*If current_reads > 0 Then*
*Dim num_inserted As Integer = ArrayBoundInsertHelper(theInsertCommand, holder, current_reads)*
*rtn_insert_count = rtn_insert_count + num_inserted*
*End If*
*mConnection.Close()*
*End Using*
*Return rtn_insert_count*
*End If*
*End Function*
*''' <summary>*
*''' Helper to help bulk insert into table on Oracle*
*''' </summary>*
*''' <param name="theInsertCommand"></param>*
*''' <param name="holder"></param>*
*''' <param name="_boundCount"></param>*
*''' <returns></returns>*
*''' <remarks></remarks>*
*Private Shared Function ArrayBoundInsertHelper(ByRef theInsertCommand As OracleCommand, ByRef holder As Object()(), ByVal _boundCount As Integer) As Integer*
*Dim rtn_val As Integer = 0*
*theInsertCommand.ArrayBindCount = _boundCount*
*For i As Integer = 0 To theInsertCommand.Parameters.Count - 1*
*'we don't want to insert the key as that is automatically incremented by Oracle*
*If Not theInsertCommand.Parameters(i).SourceColumn = "ACR_ACCT_REFRESH_ID" Then*
*theInsertCommand.Parameters(i).Value = holder(i)*
*theInsertCommand.Parameters(i).Direction = ParameterDirection.Input*
*End If*
*Next*
*rtn_val = theInsertCommand.ExecuteNonQuery()*
*Return rtn_val*
*End Function*
I am trying to use something similar but a *BULK UPDATE* and I am getting an error: *Couldn’t convert system.int32 to system.array*
I have a table consisting of 15 columns and hundreds to thousands of records that one or more columns have changed and I want to update these values in bulk.
I thought of creating something like this:
TYPE subset_rt IS RECORD
( empno emp.empno%TYPE
, ename emp.ename%TYPE
, hiredate emp.hiredate%TYPE
, deptno emp.deptno%TYPE );
(for all 15 columns and then use a FORALL, but then how do I call this from VB.NET?)
Is there a better way to create a BULK UPDATE from VB.NET to update multiple rows to an Oracle table?
Note that I am using Oracle 10.2g.