Organizational Research By

Surprising Reserch Topic

Experts Most Trusted Topic

springs stored procedure results coming back from procedure always empty

springs stored procedure results coming back from procedure always empty  using -'java,oracle,stored-procedures,jdbc,spring-jdbc'

I am using Spring's JdbcTemplate and StoredProcedure classes.  I am having trouble getting the stored procedure class to work for me.

I have a stored procedure on an oracle database.  Its signature is

(user_cursor OUT Pkg_Types.cursor_type
 , section_option_in IN Varchar2
 , section_in IN Varchar2) AS ....


TYPE cursor_type IS REF CURSOR;

I have create the following stored procedure class to get information from the oracle procedure

    private class MyStoredProcedure extends StoredProcedure
    public MyStoredProcedure(JdbcTemplate argJdbcTemplate)
        super(argJdbcTemplate, "PRC_GET_USERS_BY_SECTION");
        declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR));
        declareParameter(new SqlParameter("input1", Types.VARCHAR));
        declareParameter(new SqlParameter("input2", Types.VARCHAR));

    public Map<String, Object> execute() {

        Map<String, Object> inParams = new HashMap<String, Object>();
        inParams.put("input1", "BG");
        inParams.put("input2", "FE");
        Map output = execute(inParams);
        return output;

I am calling this in a method in one of my DAO classes

    public List<String> getUserListFromProcedure() throws BatchManagerException
    MyStoredProcedure sp = new MyStoredProcedure( this.jdbcTemplate );
    Map<String, Object> result = new HashMap<String, Object>();
        result = sp.execute();

    catch( DataAccessException dae)

    return null;

However the size of the map is always 0, so nothing comes back.  I know that there are rows on the database which match my input criteria.  Also I had code working which used java.sql.CallableStatement to interact with the oracle stored proc - so the proc is good.  Is it wrong to mix OraceleTypes.CURSOR with Spring's Stored Procedure?  What else can I use?  I also tried SqlReturnResultSet and that didn't work either.

asked Sep 14, 2015 by EdwardoHughv
0 votes

Related Hot Questions

Government Jobs Opening