How to call Oracle function or stored procedure using spring persistence framework?

Assuming you are referring to JdbcTemplate:

jdbcTemplate.execute(    new CallableStatementCreator() {        public CallableStatement createCallableStatement(Connection con) throws SQLException{            CallableStatement cs = con.prepareCall("{call MY_STORED_PROCEDURE(?, ?, ?)}");            cs.setInt(1, ...); // first argument            cs.setInt(2, ...); // second argument            cs.setInt(3, ...); // third argument            return cs;        }    },    new CallableStatementCallback() {        public Object doInCallableStatement(CallableStatement cs) throws SQLException{            cs.execute();            return null; // Whatever is returned here is returned from the jdbcTemplate.execute method        }    });

Calling a function is almost identical:

jdbcTemplate.execute(    new CallableStatementCreator() {        public CallableStatement createCallableStatement(Connection con) {            CallableStatement cs = con.prepareCall("{? = call MY_FUNCTION(?, ?, ?)}");            cs.registerOutParameter(1, Types.INTEGER); // or whatever type your function returns.            // Set your arguments            cs.setInt(2, ...); // first argument            cs.setInt(3, ...); // second argument            cs.setInt(4, ...); // third argument            return cs;        }    },    new CallableStatementCallback {        public Object doInCallableStatement(CallableStatement cs) {            cs.execute();            int result = cs.getInt(1);            return result; // Whatever is returned here is returned from the jdbcTemplate.execute method        }    });

Simpler way of calling a Oracle function in Spring is subclassing StoredProcedure like below

public class MyStoredProcedure extends StoredProcedure{    private static final String SQL = "package.function";    public MyStoredProcedure(DataSource ds){        super(ds,SQL);        declareParameter(new SqlOutParameter("param_out",Types.NUMERIC));        declareParameter(new SqlParameter("param_in",Types.NUMERIC));        setFunction(true);//you must set this as it distinguishes it from a sproc        compile();    }    public String execute(Long rdsId){        Map in = new HashMap();        in.put("param_in",rdsId);        Map out = execute(in);        if(!out.isEmpty())            return out.get("param_out").toString();        else            return null;    }}

And call it like this

@Autowired DataSource ds;MyStoredProcedure sp = new MyStoredProcedure(ds);String i = sp.execute(1l);

The Oracle function used here just takes in a numeric parameter and returns a numeric paramter.

In my opinion this is one of the easiest approaches:

public class ServRepository {    private JdbcTemplate jdbcTemplate;    private SimpleJdbcCall functionGetServerErrors;    @Autowired    public void setDataSource(DataSource dataSource) {        this.jdbcTemplate = new JdbcTemplate(dataSource);        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);        jdbcTemplate.setResultsMapCaseInsensitive(true);        this.functionGetServerErrors = new SimpleJdbcCall(jdbcTemplate).withFunctionName("THIS_IS_YOUR_DB_FUNCTION_NAME").withSchemaName("OPTIONAL_SCHEMA_NAME");    }        public String callYourFunction(int parameterOne, int parameterTwo) {            SqlParameterSource in = new MapSqlParameterSource().addValue("DB_FUNCTION_INCOMING_PARAMETER_ONE", parameterOne).addValue("DB_FUNCTION_INCOMING_PARAMETER_TWO", parameterTwo);            return functionGetServerErrors.executeFunction(String.class, in);        }}