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 15, 2015 by danellemacfarlan
0 votes

2 Answers

0 votes

The problem here is that Oracle's way of doing stored procedures is not JDBC compliant. Oracle's SPs return resultset data via OUT parameters or return values that are cursors, and they have to be handled specially. This means you cannot use any of Spring's JDBC stuff that assumes compliance with JDBC, you have to do it yourself.

In practice, this means you have to use JdbcTemplate and CallableStatementCallback, which means a lot more manual JDBC coding than you'd ideally like, but I've yet to find a way to avoid this.

On a slight aside, I rather suspect that the JDBC spec was written to conform closely to the Sybase (and, by association, SQL Server) way of doing things, because the way stored procedures are handled in JDBC is a remarkably good fit for those systems (and a poor fit for Oracle's).

answered Sep 15, 2015 by BraMoonligh
0 votes

The problem is simple if you don't pass a RowMapper while declaring the outparam which is a CURSOR. Spring is going to return {} I.e empty cursor.

 declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR)); - returns empty {}
 declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR, new ApplicationMapper()); - returns result

Where ApplicationMapper is my custom mapper which implements RowMapper.

answered Sep 15, 2015 by RandalRatcli