ODP.NET

Hi, I'm currently testing the performance of batch insertion (1 000 000 rows) on various database systems in ado.net and I encounter severe limitations when testing on oracle which confuse me.

I'm in .net 2.0, using instant client 11.1.0.6.21.
To insert data I use a DataTable and play with the UpdateBatchSize property of the DataAdapter.

For comparison, using the MySql connector I can set UpdateBatchSize to 10 000 and the insertion takes around 100s.

When I use the the Oracle connectors (I tested with the oracle and the microsoft one), I observe totally different results:


- With Oracle.DataAccess:

If I set UpdateBatchSize over 200 I get a NullReferenceException:

Exception:System.NullReferenceException: Object reference not set to an instance of an object.
at Oracle.DataAccess.Client.OracleDataAdapter.GetBatchedRecordsAffected(Int32 commandIdentifier, Int32& recordsAffected, Exception& error)
at System.Data.Common.DbDataAdapter.UpdateBatchExecute(BatchCommandInfo[] batchCommands, Int32 commandCount, RowUpdatedEventArgs rowUpdatedEvent)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at Oracle.DataAccess.Client.OracleDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

I did extensive research but didn't found anything that could explain this issue.


- With System.Data.OracleClient, I get a more expressive message (and I can batch more commands):

Exception:System.Data.OracleClient.OracleException: ORA-06550: line 1594, column 97:
PLS-00123: program too large

It occurs when I try to set UpdateBatchSize over 700.


I would be very grateful if someone could enlighten me on this issue as I'm at a total loss.