为 Oracle 存储过程使用 Spring JDBC 当 SP 抛出 ORA-20118 时，我得到一个 ORA-02055
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2178679/
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
Using Spring JDBC for Oracle Stored Procedure I get a ORA-02055 when SP throws ORA-20118
提问by Jim Barrows
ORA-20118 is a custom exception from the stored procedure. The stored procedure runs just fine from PL-SQL developer, so the problem is in Spring. What I need to do is to get Spring to rollback the SP when it gets the ORA-20118 exception back from the SP. How do I do that? or maybe just get spring to correctly handle the 20118 code coming back. That would work too.
ORA-20118 是来自存储过程的自定义异常。PL-SQL 开发人员的存储过程运行得很好，所以问题出在 Spring 中。我需要做的是让 Spring 在从 SP 获取 ORA-20118 异常时回滚 SP。我怎么做？或者也许只是让 spring 正确处理返回的 20118 代码。那也行。
There is no transaction management being done.
没有进行事务管理。
Da code:
大码：
@Repository
public class ProgramMaintenance extends StoredProcedure {
//bunch of static final param names go here
@Autowired(required = true)
public ProgramMaintenance(@Qualifier("osirisDataSource") final DataSource ds) {
super(ds, SQL);
OracleStoredProcedureExceptionHandler exceptionHandler = new OracleStoredProcedureExceptionHandler();
exceptionHandler.setDataSource(ds);
this.getJdbcTemplate().setExceptionTranslator(exceptionHandler);
addParameters();
this.setFunction(false);
compile();
}
public void execute( //parameters ) {
//Put the input map together here
execute(inputMap);
}
So here is the exception handler, along with notes of what's going on:
所以这里是异常处理程序，以及正在发生的事情的注释：
public class OracleStoredProcedureExceptionHandler extends SQLErrorCodeSQLExceptionTranslator {
protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) {
if (logger.isDebugEnabled()) {
logger.debug("customTranslate(String, String, SQLException) - start"); //$NON-NLS-1$
}
//The error code at this point is ORA-02055 with the cause as ORA-20118,
//So, the case statement drops straight through.
switch (sqlex.getErrorCode()) {
case 20113 : return new ProgramNotAtCampusException(task + " " +sql, sqlex);
case 20118 : return new ProgramNotApprovedForStateOfResidence(task + " " +sql, sqlex);
default: return null;
}
}
And the stack trace:
和堆栈跟踪：
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call isis.program_maintenance.program_maintenance(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-02055: distributed update operation failed; rollback required
ORA-20118: VALIDATION ERROR:This program is not approved for the state this student resides in.
ORA-06512: at "ISIS.APPLY_WEB_INTEGRATION", line 372
ORA-06512: at "ISIS.APPLY_WEB_INTEGRATION", line 1332
ORA-06512: at "ISIS.APPLY_WEB_INTEGRATION", line 2842
ORA-06512: at "ISIS.PROGRAM_MAINTENANCE", line 66
ORA-06512: at line 1
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985)
at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:117)
at com.apollo.aw.dao.storedProcedures.programMaintenance.ProgramMaintenance.execute(ProgramMaintenance.java:125)
at test.eval.dao.storedprocedures.programMaintenance.TestProgramMaintenance.testExecuteForORA20118(TestProgramMaintenance.java:64)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
at junit.framework.TestResult.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:83)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
采纳答案by Jim Barrows
And the right answer is.... the test itself was in error, and Spring was doing the right thing.. SIGHThis:
正确的答案是......测试本身是错误的，而 Spring 正在做正确的事情...... SIGH这个：
@Test(expected=ProgramNotAtCampusException.class)
was not working correctly, however wrapping it in a try catch block and ignoring the error, works just fine.. SIGH.
无法正常工作，但是将其包装在 try catch 块中并忽略错误，效果很好.. SIGH。
回答by Pascal Thivent
What I need to do is to get Spring to rollback the SP when it gets the ORA-20118 exception back from the SP.
我需要做的是让 Spring 在从 SP 获取 ORA-20118 异常时回滚 SP。
For declarative transactions, you can refer to this sectionabout rollback rules. But in short, just throw an exception that will not get caught in a try/catch block.
对于声明式事务，可以参考本节回滚规则。但简而言之，只需抛出一个不会被 try/catch 块捕获的异常。
回答by Gary Myers
Don't worry, be happy. If Oracle raises an exception, any uncommitted changes made by that call will be automatically rolled back. It doesn't matter if that call is an insert,update,delete,merge or stored proedure call, the statement has failed and the atomic nature of the call requires that the database state is restored to the point before the calls started.
别担心，开心就好。如果 Oracle 引发异常，则该调用所做的任何未提交的更改都将自动回滚。无论该调用是插入、更新、删除、合并还是存储过程调用，语句都失败了，并且调用的原子性要求将数据库状态恢复到调用开始之前的状态。
> create table test (id number);
Table created.
> create or replace procedure ins_test is
08:42:46 2 begin
08:42:48 3 insert into test values (10);
08:42:55 4 raise too_many_rows;
08:43:00 5 end;
08:43:01 6 /
Procedure created.
> exec ins_test;
BEGIN ins_test; END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "GARY.INS_TEST", line 4
ORA-06512: at line 1
> select * from test;
no rows selected
回答by Brian
On the surface it looks like everything is functioning exactly the way it should. Can you post the spring-config.xml entries for the transaction Manager for this datasource?
从表面上看，一切似乎都在按照应有的方式运行。您可以发布此数据源的事务管理器的 spring-config.xml 条目吗？
By default, RuntimeException instances cause a rollback in Spring. There are several programmatic ways to rollback (but the transaction manager can have the most common attributes set to avoid this kind of code:
默认情况下，RuntimeException 实例会导致 Spring 中的回滚。有几种编程方式来回滚（但事务管理器可以设置最常见的属性来避免这种代码：
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
You should read the Spring docs on Transaction Managementfor thorough overview.
您应该阅读有关事务管理的 Spring 文档以获得全面的概述。
