代码之家 › 专栏 › 技术社区 › Jim Barrows

使用SpringJDBC for Oracle存储过程,当sp抛出ora-20118时,我得到一个ora-02055

jdbc stored-procedures spring oracle java

Jim Barrows · 技术社区 · 15 年前

ORA-20118是存储过程中的自定义异常。存储过程在pl-sql开发人员那里运行得很好,所以问题出现在Spring中。我需要做的是让spring在从sp返回ora-20118异常时回滚sp。我该怎么做?或者让Spring正确处理20118代码。那也行。

没有进行事务管理。

DA代码:

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

4 回复 | 直到 15 年前

Pascal Thivent 15 年前

我需要做的是让spring在从sp返回ora-20118异常时回滚sp。

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

Brian 15 年前

从表面上看,一切正常运转。可以发布此数据源的事务管理器的spring-config.xml条目吗?

默认情况下,runtimeexception实例会在春季导致回滚。回滚有几种编程方法(但事务管理器可以设置最常见的属性以避免此类代码:

TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

你应该读一下Spring文档 Transaction Management 全面概述。

Gary Myers 15 年前

别担心,开心点。如果Oracle引发异常,则该调用所做的任何未提交的更改都将自动回滚。无论该调用是insert、update、delete、merge还是stored proedure调用,语句都失败了,调用的原子性要求将数据库状态还原到调用开始之前的状态。

> 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

skaffman 15 年前

正确的答案是……测试本身出错了,Spring做的是正确的事情。叹息这是:

 @Test(expected=ProgramNotAtCampusException.class)

工作不正常,但是将其包装在try-catch块中并忽略错误,工作正常。叹息 .