ORA24381: error(s) in array DML

使用ODP.NET 批量插入时，将cmd.ArrayBindCount 设置为10万时出错，经搜索得知，原来使用array DML operations一次最多插入65535条=2^16。但是据我实验一次最多插入65534条。

本人使用的是Oracle 10.1.0.2。不知道之后的版本修复了没有！

以下是插入6万条记录，各种方法花费的时间。

ordinaryInsert

 1 static void ordinaryInsert(int[] ids, string[] names)
 2         {
 3             using (OracleConnection connection = new OracleConnection(connectString))
 4             {
 5                 connection.Open();
 6                 using (OracleCommand cmd = connection.CreateCommand())
 7                 {
 8                     string sqlInsert = "insert into person values(:pId,:name)";
 9                     cmd.CommandText = sqlInsert;
10                     OracleParameter paraId =
11                         cmd.Parameters.Add("pId", OracleDbType.Int32);
12                     paraId.Direction = ParameterDirection.Input;
13                     OracleParameter paraName =
14                         cmd.Parameters.Add("name", OracleDbType.NVarchar2, 50);
15                     paraName.Direction = ParameterDirection.Input;
16 
17                     Stopwatch watch = new Stopwatch();
18                     watch.Start();
19 
20                     for (int i = 0; i < ids.Length; i++)
21                     {
22                         paraId.Value = ids[i];
23                         paraName.Value = names[i];
24 
25                         cmd.ExecuteNonQuery();
26                     }
27 
28                     watch.Stop();
29                     Console.WriteLine("ellapsed time {0} ms", watch.ElapsedMilliseconds);
30                 }
31             }
32         }

ellapsed time 67309 ms

batchInsert

 1 static void batchInsert(int[] ids, string[] names)
 2         {
 3             using (OracleConnection connection = new OracleConnection(connectString))
 4             {
 5                 connection.Open();
 6                 using (OracleCommand cmd = connection.CreateCommand())
 7                 {
 8                     string sqlInsert = "insert into person values(:pId,:name)";
 9                     cmd.CommandText = sqlInsert;
10                     cmd.ArrayBindCount = ids.Length;
11                     OracleParameter paraId =
12                         cmd.Parameters.Add("pId", OracleDbType.Int32);
13                     paraId.Direction = ParameterDirection.Input;
14                     OracleParameter paraName =
15                         cmd.Parameters.Add("name", OracleDbType.NVarchar2, 50);
16                     paraName.Direction = ParameterDirection.Input;
17                     paraId.Value = ids;
18                     paraName.Value = names;
19 
20                     Stopwatch watch = new Stopwatch();
21                     watch.Start();
22                     cmd.ExecuteNonQuery();
23                     watch.Stop();
24                     Console.WriteLine("ellapsed time {0} ms", watch.ElapsedMilliseconds);
25                 }
26             }
27         }

ellapsed time 923 ms

batchInsertViaProcedure

 1 static void batchInsertViaProcedure(int[] ids, string[] names)
 2         {
 3             using (OracleConnection connection = new OracleConnection(connectString))
 4             {
 5                 connection.Open();
 6                 using (OracleCommand cmd = connection.CreateCommand())
 7                 {
 8                     cmd.CommandText = "insertPerson";
 9                     cmd.CommandType = CommandType.StoredProcedure;
10                     cmd.ArrayBindCount = ids.Length;
11                     OracleParameter paraId =
12                         cmd.Parameters.Add("p_pId", OracleDbType.Int32);
13                     paraId.Direction = ParameterDirection.Input;
14                     OracleParameter paraName =
15                         cmd.Parameters.Add("p_name", OracleDbType.NVarchar2, 50);
16                     paraName.Direction = ParameterDirection.Input;
17 
18                     paraId.Value = ids;
19                     paraName.Value = names;
20 
21                     Stopwatch watch = new Stopwatch();
22                     watch.Start();
23                     cmd.ExecuteNonQuery();
24                     watch.Stop();
25                     Console.WriteLine("ellapsed time {0} ms", watch.ElapsedMilliseconds);
26                 }
27             }
28         }

ellapsed time 5116 ms

batchInsert和batchInsertViaProcedure花费时间少于ordinaryInsert是毋庸置疑的，但是batchInsertViaProcedure却比batchInsert花费更长的时间有点不能理解。难道预编译存储在数据库中的存储过程比在程序库缓冲区Oracle SQL sharing中的语句执行的更慢？难道Oracle中没有缓存procedure的可执行代码，导致每次调用都要加载，还是函数调用开销？不知道，留着疑问慢慢摸索O(∩_∩)O~

最后谢谢http://www.cnblogs.com/isline/archive/2010/08/31/1813722.html，文章不错！