Announcement Announcement Module
No announcement yet.
StoredProcedure/Oracle Cursor Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure/Oracle Cursor

    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",
                declareParameter(new SqlReturnResultSet("output",
                        new RowMapper() {
                        public Object mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                            MyObject myObject = new MyObject();
                            return myObject 
            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!

  • #2
    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.