Spring's Stored Procedure - results coming back from procedure always empty

Question

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

CREATE OR REPLACE PROCEDURE PRC_GET_USERS_BY_SECTION
(user_cursor OUT Pkg_Types.cursor_type
 , section_option_in IN Varchar2
 , section_in IN Varchar2) AS ....

where

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));
        compile();          
    }


    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>();
    try
    {
        result = sp.execute();
    }

    catch( DataAccessException dae) 
    {

    }
    System.out.println(result.size());
    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.

Aphrodisia

Answer 1

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).

Deshawndesi

Hi, Thanks for your feedback. I got this working with my Oracle stored procedure. The problem was with the output parameter I had declared. The following works declareParameter( new SqlOutParameter( "output", oracle.jdbc.OracleTypes.CURSOR, new RowMapper() { public String mapRow(ResultSet argResults, int argRowNum ) throws SQLException { return argResults.getString(1); } }) ); This is working for me now. Thanks – Aphrodisia

You should have answered your own question and selected it as the accepted answer. Thanks for posting anyway. – Butyrate

Answer 2

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.

Lishalishe