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

Question

Hi,

I am using Spring persistence framework for my project. I want to call oracle function or stored procedure from this framework.

Can anybody suggest how can I achieve this.

Please give solution for both * oracle function and *stored procedure.

Thanks.

Answer 1

Assuming you are referring to JdbcTemplate:

jdbcTemplate.execute(
    new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) {
            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) {
            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
        }
    }
);

Answer 2

look in to this site http://www.javaj2eekp.blogspot.com/

Answer 3

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

public class func extends StoredProcedure{
private static final String SQL = "package.function";

public sproc(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;
Func f = new Func(ds);
String i = f.execute(1l);

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

Answer 4

Thank you very much for your answer, Adam Paynter.