StoredProcedure/Oracle Cursor

[cornflakeGirl]

Hi All,

Here is what I am trying to accomplish -- I have a stored procedure that takes in two inputs, returns an Oracle cursor. From the result set, I want to map each row to a custom object, and then return an array of these objects.

This is what I've come up with so far:

public class MyDAOImpl implements MyDAO {
    private JdbcTemplate jdbcTemplate;

    public MyObject[] getObjects(String input1, String input2)
        throws ServiceLogicException {
        GetObjects sproc = new GetObjects(jdbcTemplate.getDataSource());
        Map results = sproc.execute(siteID, finalDestinationID);

        ResultSet objects = results.get("output");

        // Now what?!? :(
    }

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    private class GetObjects extends StoredProcedure {
        public static final String PROC_NAME = "stored procedure name";

        public GetObjects(DataSource ds) {
            super(ds, PROC_NAME);
            declareParameter(new SqlParameter("input1", Types.VARCHAR));
            declareParameter(new SqlParameter("input2",
                    Types.VARCHAR));
            declareParameter(new SqlReturnResultSet("output",
                    new RowMapper() {
                    public Object mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                        MyObject myObject = new MyObject();
                        myObject.setProp1(rs.getLong(1));
                        myObject.setProp2(rs.getLong(2));

                        return myObject 
                    }
                }));
            compile();
        }

        public Map execute(String input1, String input2){
            Map inParameters = new HashMap();
            inParameters.put("input1", input1);
            inParameters.put("input2", input2);

            return super.execute(inParameters);
        }
    }
}

Am I on the right track? Is it correct to map each row in a result set to an object, and then assume that my new result set will be a collection of objects? If so, how do I convert the new result set to an array of objects?

Please help! Thanks!

[trisberg]

The out parameter for the Oracle stored procedure should be declared as an SqlOutParameter with a type of OracleTypes.CURSOR like:

declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR, new RowMapper() ...

The resulting Map will have an entry named "output" which is a List of the objects you created. You should be able to use toArray() method after casting this to a List.