Call stored procedures or functions in Spring

A easy way to call stored procedure or function is to use Spring's SimpleJdbcCall class. It wraps up some convenient features. Most likely, you only need to provide in/out parameters. It uses JdbcTemplte internally.

Here is an example to call stored procedure.
SimpleJdbcCall caller = new SimpleJdbcCall(getDataSource())
.withCatalogName("PKG_OPPIN")
.withFunctionName("pc_unsubmit_from_rr");

SqlParameterSource params = new MapSqlParameterSource()
.addValue("p_case_seq", caseSeq)
.addValue("p_task_seq", taskSeq);

// this sp has no return
caller.execute(params);

Here is an example to call function.
SimpleJdbcCall caller = new SimpleJdbcCall(getDataSource())
.withCatalogName("PKG_OPPIN")
.withFunctionName("fn_get_bean_target_date");

MapSqlParameterSource params = new MapSqlParameterSource()
.addValue("p_dpkg_seq", dataPackageNumber);

Date beanTargetDate = caller.executeFunction(Date.class, params);

return beanTargetDate;


To avoid a call to get metadata, you can declare all parameter explicitly. Here is an example.

SimpleJdbcCall caller = new SimpleJdbcCall(getDataSource())
.withCatalogName("PKG_REG_REVIEW")
.withFunctionName("fn_get_task_complete_date")
.withoutProcedureColumnMetaDataAccess()
.declareParameters(
new SqlOutParameter("v_maxdate", Types.DATE),
new SqlParameter("p_case_seq", Types.INTEGER),
new SqlParameter("p_task_seq", Types.INTEGER)
);

MapSqlParameterSource params = new MapSqlParameterSource()
.addValue("p_task_seq", taskSeq)
.addValue("p_case_seq", caseSeq);

Date taskCompletionDate = caller.executeFunction(Date.class, params);

return taskCompletionDate;

The use of withoutProcedureColumnMetaDataAccess() disables metadata reading. Note that the order matters in declareParameters() - it should match exactly the order of the parameters when the procedure/function is called. Another thing to note is that you don't need to use useInParameters() with withoutProcedureColumnMetaDataAccess() because it is only for filtering the metadata.

Another way to call sp/function is to use Spring's StoredProcedure class. But you will need to create a sub-class for it since StoredProcedure is abstract. Here is an example.

MyStoredFunction fn = new MyStoredFunction(getJdbcTemplate(),
"PKG_REG_REVIEW.fn_get_task_complete_date");
fn.declareParameter(new SqlOutParameter("v_maxdate", Types.DATE));
fn.declareParameter(new SqlParameter("p_case_seq", Types.INTEGER));
fn.declareParameter(new SqlParameter("p_task_seq", Types.INTEGER));
fn.compile();

Map params = new HashMap();
params.put("p_case_seq", caseSeq);
params.put("p_task_seq", taskSeq);

Map result = fn.execute(params);
Date taskCompletionDate = (Date) result.get("v_maxdate");
The execute() method always return a Map. You need to get and cast to get the returned data.