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

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 14, 2015 by EdwardoHughv
0 votes
7 views



Related Hot Questions



Government Jobs Opening


...