oracle BatchUpdateException：批处理不会终止
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4724957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BatchUpdateException: the batch will not terminate
提问by transient_loop
I have an application which processes a very large file and sends data to an oracle database (using Java 6, oracle 9).
我有一个应用程序，它处理一个非常大的文件并将数据发送到 oracle 数据库（使用 Java 6、oracle 9）。
In a loop, I use a PreparedStatement psand create all SQL statements generated with ps.addBatch().
在循环中，我使用 aPreparedStatement ps并创建所有使用ps.addBatch().
I have a situation where a BatchUpdateException bueis thrown somewhere during the ps.executeBatch(). At that point, the batch stops to be executed.
我有一种情况，BatchUpdateException bue在ps.executeBatch(). 此时，批处理停止执行。
I'd like the batch execution to continue, so that I can then check on failed updates in a method processUpdateCounts(bue.getUpdateCounts()).
我希望批处理继续执行，以便我可以在方法中检查失败的更新processUpdateCounts(bue.getUpdateCounts())。
The javadoc about class BatchUpdateException says:
关于 BatchUpdateException 类的 javadoc 说：
After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continueto process the remaining commands in the batch.
在批量更新中的命令无法正确执行并抛出 BatchUpdateException 后，驱动程序可能会也可能不会继续处理批处理中的剩余命令。
Is there a way to enforce continuation or do I need to alter my program so that it will execute the statement individually?
有没有办法强制执行继续，或者我是否需要更改我的程序以便它单独执行语句？
采纳答案by transient_loop
Just found this link: JDBC Batch Update Problem
刚刚找到这个链接： JDBC Batch Update Problem
Apparently, it says there there is
显然，它说有
NO WAYWITH ORACLE BATCH JDBC to proceed after first failure,
NO WAY和ORACLE JDBC批处理第一次失败之后进行，
thus I am resorting to sending the inserts one by one. Thank you
因此，我将一一发送插入内容。谢谢
(sorry for not looking better to find the link above before).
（抱歉，之前没有更好地找到上面的链接）。
回答by Vincent Malgrat
there is a workaround that would allow you to use the batch feature. Instead of executing a simple INSERT statement, you can execute a PL/SQL block that will deal with the error appropriately:
有一种解决方法可以让您使用批处理功能。您可以执行一个 PL/SQL 块来适当地处理错误，而不是执行简单的 INSERT 语句：
BEGIN
INSERT INTO your_table VALUES (?,?,...?);
EXCEPTION
WHEN OTHERS THEN
/* deal with the error. For example, log the error id and error msg
so that you can list them after the batch */
INSERT INTO error_table VALUES (?, sqlerrm);
END
The performance should be on par with the batch insert (should be faster than individual execution of the statements). You could also call a stored procedure instead of a PL/SQL block.
性能应该与批量插入相当（应该比单独执行语句快）。您还可以调用存储过程而不是 PL/SQL 块。
回答by Markus Winand
Oracle itself can, see here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci04sql.htm#sthref616
Oracle本身可以，看这里：http: //download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci04sql.htm#sthref616
However, it doesn't seem that this functionality is exposed to JDBC, not even in the oracle specific classes.
然而，这个功能似乎没有暴露给 JDBC，甚至在 oracle 特定的类中也没有。
Because of the rather useless JDBC error handling ("the driver may or may not continue"), I'm always setting a savepoint before the batch, and performing an rollback to that point on error. That's the only JDBC compliant way to establish a known state after an Oracle Batch Error--as far as I know.
由于相当无用的 JDBC 错误处理（“驱动程序可能会或可能不会继续”），我总是在批处理之前设置一个保存点，并在出错时执行回滚到那个点。据我所知，这是在 Oracle 批处理错误后建立已知状态的唯一符合 JDBC 的方法。
回答by Sanjay T. Sharma
Since the specification doesn't seem to mandate it (as clearly shown by the Javadoc), any "forced" continuation would have to be done on per-driver basis. A simple standard-compliant workaround would be to check the getUpdateCounts()returned array and "re-run" the batch for those statements which failed. You can make this approach a bit more sophisticated by putting in a logic for the number of retries.
由于规范似乎没有强制要求（正如 Javadoc 清楚显示的那样），任何“强制”延续都必须在每个驱动程序的基础上完成。一个简单的符合标准的解决方法是检查getUpdateCounts()返回的数组并为那些失败的语句“重新运行”批处理。您可以通过为重试次数设置逻辑来使这种方法更加复杂。
Sure, this seems a bit messy (keeping track of the "batch" added and then checking the output) but would work across all databases and driver implementations. Just a thought...
当然，这看起来有点混乱（跟踪添加的“批处理”，然后检查输出），但适用于所有数据库和驱动程序实现。只是一个想法...
