Bulk insert from datatable in to Oracle using Oracle.DataAccess & VB.net

I am开发者_如何学编程 reading a csv file in to a datatable in vb.net and making a few checks and appending an extra column. I then want to perform a bulk insert using microsofts Oracle.DataAccess (no choice in this) to an Oracle database.

what would be the best way to perform this as there is no bulkImport like in SQLserver.

thanks

Why a bulk insert rather than a regular insert ?

If it is to avoid generating redo log entries, your best best is a conventional insert into a global temporary table [which doesn't generate redo because, since it is temporary, it never needs to be recovered]. Then, at the end, an INSERT/*+APPEND */ into the real table from the temporary table. If the real table is defined as NOLOGGING, the append hint will tell it not to generate redo.

I have been searching on similar lines, but hit a dead-end. I understand from a statement by Pablo Castro, Program Manager - ADO .NET Team of Microsoft Corp, that SqlBulkCopy is included in ADO.NET 2.0, in the .NET Provider for SQL Server (SqlClient). They do not support bulk-copy in their OracleClient provider.

despite 9 years have passed since the question has been made, I think the correct answer is (it worked for me)

 Public Shared Sub PerformBulkCopy(ByVal dt As DataTable, ByVal sTableName As String)
    Try
        Using Conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString)
            Conn.Open()

            Using s As Oracle.DataAccess.Client.OracleBulkCopy = New Oracle.DataAccess.Client.OracleBulkCopy(Conn)

                s.DestinationTableName = sTableName
                s.WriteToServer(dt)
                s.Close()

            End Using

            Conn.Close()
        End Using
    Catch err As Exception
        Debug.Print(err.Message)
        commonFunctions.OutLog(err.Message)
    End Try
End Sub