3 Ways to Call an Oracle Function From Spring

If you're working with JDBC in Spring, chances are you'll work with JdbcTemplate and the related SimpleJdbcCall.  If you need to integrate stored procedures and database functions, you can try SqlFunction or extend StoredProcedure.
This post presents three ways to invoke an Oracle PL/SQL function using Spring.  These examples (except the PL/SQL block at the end of the post) are portable, so they should work with any datasource.  The datasource used in this example is defined in Spring.

Don't Call a Function

If your function is simple, consider replacing it with a call to SqlFunction.

//
// example 1 - Replace the function
//

SqlFunction func = new SqlFunction(dataSource, "SELECT COUNT(*) FROM source_system WHERE source_nm=?", new int[]{Types.VARCHAR});

int sourceCount = func.run("AXT");

Use SimpleJdbcCall

This object will actually invoke a PL/SQL function and contains a syntax that can handle more parameters.

//
// example 2
//

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);

SimpleJdbcCall call = 
new SimpleJdbcCall(jdbcTemplate).withFunctionName("LOOKUP_SOURCE_SYSTEM_COUNT");

SqlParameterSource in = new MapSqlParameterSource().addValue("p_source_nm", "AXT");

BigDecimal sourceCount_bd = call.executeFunction(BigDecimal.class, in);

Long sourceCount = (sourceCount_bd!=null)?sourceCount_bd.longValue():new Long(-1L);

Extend StoredProcedure

Extending StoredProcedure encapsulates the function into a single object.  Callers need to instantiate the class with a datasource and call 'execute'.  The parameters and return values can be handled with a RowMapper (as can SimpleJdbc).  Complex database objects like REF CURSOR are best supported with a RowMapper.

//
// example 3 - Calling a StoredProcedure
//

LookupSourceSystemCountFunction func = 
    new LookupSourceSystemCountFunction(dataSource);
Long sourceCount = func.execute("AXT");

// ***** LookupSourceSystemCountFunction.java
public class LookupSourceSystemCountFunction extends StoredProcedure {

   /**
    * Constant name of function plus name given to return value 
    * stored in output map
    */
  private static final String LOOKUP_SOURCE_SYSTEM_COUNT =    
       "LOOKUP_SOURCE_SYSTEM_COUNT";

  public LookupSourceSystemCountFunction(DataSource _datasource) {
   super(_datasource, LOOKUP_SOURCE_SYSTEM_COUNT);
   setFunction(true);
   declareParameter( new SqlOutParameter(LOOKUP_SOURCE_SYSTEM_COUNT, 
         Types.NUMERIC) );
   declareParameter(new SqlParameter("p_source_nm", Types.VARCHAR));
   compile();
  }

  @SuppressWarnings("unchecked")
  public long execute(String requestorId) {

   Map inparams = new HashMap(1);

   inparams.put("p_source_nm", requestorId);

   Map outparams = execute(inparams);

   BigDecimal sourceCount_bd = 
       (BigDecimal)outparams.get(LOOKUP_SOURCE_SYSTEM_COUNT);

   return 
      (sourceCount_bd!=null)?sourceCount_bd.longValue():new Long(-1L);
  }
}

For the caller, say a DAO, this makes a good two-line constructor/execute pair.

The Function

The following is the sample PL/SQL function refrenced in the Java examples. 

--
-- Oracle function
--

FUNCTION LOOKUP_SOURCE_SYSTEM_COUNT (
  p_source_nm IN VARCHAR2
)
RETURN NUMBER
IS
   ret_val NUMBER :=0;
BEGIN
   SELECT COUNT(*) INTO ret_val FROM source_system WHERE source_nm=p_source_nm;
   RETURN ret_val;
END 

There are many permutations of calls, parameters, and result set mappers that you can make with Spring.  SimpleJdbcCall is a good starting point because of its type safety.  A StoredProcedure provides the strongest encapsulation of the call, hiding the parameter and result set details.