Using Spring JDBC for Oracle Stored Procedure I get a ORA-02055 when SP throws ORA-20118

Question

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.

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

Answer 1

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?

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:

TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

You should read the Spring docs on Transaction Management for thorough overview.

Answer 2

What I need to do is to get Spring to rollback the SP when it gets the ORA-20118 exception back from the SP.

For declarative transactions, you can refer to this section about rollback rules. But in short, just throw an exception that will not get caught in a try/catch block.

Answer 3

And the right answer is.... the test itself was in error, and Spring was doing the right thing.. SIGH This:

 @Test(expected=ProgramNotAtCampusException.class)

was not working correctly, however wrapping it in a try catch block and ignoring the error, works just fine.. SIGH.

Answer 4

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.

> 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