Organizational Research By

Surprising Reserch 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

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.
    
asked Sep 15, 2015 by danellemacfarlan
0 votes
58 views



Related Hot Questions



Government Jobs Opening


...