ORA-00604在TransactionScope内批量插入时发生错误 [英] ORA-00604 error while batch insertion inside TransactionScope

问题描述

我正在尝试在TransactionScope内使用ADO.NET向我的Oracle数据库中批量插入100k +项目.像这样:

I am trying to batch insert 100k+ items to my Oracle db using ADO.NET inside a TransactionScope. Like this:

using (TransactionScope transaction = new TransactionScope())
{
    while(/* Pagination logic - send insertion command on every 250 items */)
    {
        using (OracleCommand command = new OracleCommand(query, connection))
        {
            command.ArrayBindCount = 250;

            //Add parameters
            command.Parameters.Add(":BLAH", OracleDbType.Long);
            command.Parameters[0].Value = LUC.ToArray();

            command.ExecuteNonQuery(); //Error occurs here after N-times inside while
        }
    }
    transaction.Complete();
}

对于低于此(10k-30k)的项目，交易成功完成. 但是，对于较高项(例如100k)，我会得到 ORA-00604:在递归SQL级别％s上发生错误.

For items lower than this (10k-30k) transaction is completed successfully. However for higher items (like 100k) I get ORA-00604: error occurred at recursive SQL level %s.

如果我完全删除了TransactionScope，则在任何项目尺寸下都不会出现任何错误，它可以正常工作.

If I remove TransactionScope altogether, I don't get any error with any item size, it just works.

如何使TransactionScope处理大量项目?

How can I make TransactionScope work with huge number of items?

推荐答案

结果是，这是一个事务性超时问题.

Turns out, it was a transactional timeout problem.

增加超时后，我已成功插入列表:

After I increased the timeout, I have inserted my list successfully:

using (TransactionScope transaction = 
         new TransactionScope(TransactionScopeOption.Required, 
                 new TimeSpan(0, 30, 0))) //30 minute timeout limit

这篇关于ORA-00604在TransactionScope内批量插入时发生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！