Bulk Insert to Oracle using .NET

I'm loading 50,000 records in 15 or so seconds using Array Binding in ODP.NET

It works by repeatedly invoking a stored procedure you specify (and in which you can do updates/inserts/deletes), but it passes the multiple parameter values from .NET to the database in bulk.

Instead of specifying a single value for each parameter to the stored procedure you specify an array of values for each parameter.

Oracle passes the parameter arrays from .NET to the database in one go, and then repeatedly invokes the stored procedure you specify using the parameter values you specified.

http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

/Damian

I recently discovered a specialized class that's awesome for a bulk insert (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! It takes a datatable as a parameter, then you call WriteTOServer method...it is very fast and effective, good luck!!

The solution of Rob Stevenson-Legget is slow because he doesn't bind his values but he uses string.Format( ).

When you ask Oracle to execute a sql statement it starts with calculating the has value of this statement. After that it looks in a hash table whether it already knows this statement. If it already knows it statement it can retrieve its execution path from this hash table and execute this statement really fast because Oracle has executed this statement before. This is called the library cache and it doesn't work properly if you don't bind your sql statements.

For example don't do:

int n;

    for (n = 0; n < 100000; n ++)    {      mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);      mycommand.ExecuteNonQuery();    }

but do:

      OracleParameter myparam = new OracleParameter();      int n;      mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";      mycommand.Parameters.Add(myparam);      for (n = 0; n < 100000; n ++)      {        myparam.Value = n + 1;        mycommand.ExecuteNonQuery();      }

Not using parameters can also cause sql injection.