java - 将Spring JDBC用于Oracle存储过程，当SP抛出ORA-20118时，我得到ORA-02055

ORA-20118是来自存储过程的自定义异常。该存储过程可以从PL-SQL开发人员正常运行，因此问题出在Spring。我需要做的是让Spring从SP返回ORA-20118异常时回滚SP。我怎么做？或只是让Spring正确处理20118代码回来。那也可以。

没有完成事务管理。

大码：

@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);
}

所以这是异常处理程序，以及发生了什么的注释：

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;
        }

    }

和堆栈跟踪：

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$1.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)

最佳答案

我需要做的是让Spring从SP返回ORA-20118异常时回滚SP。


对于声明式事务，可以参考this section有关回滚规则。简而言之，只需抛出一个不会被try / catch块捕获的异常。