Stored procedure returning Ref cursor and a scalar value

Question

I have a requirement in our application where in the stored procedure is returning 1 Ref Cursor and 1 scalar parameter i.e. Status Code.

Now I am using Spring API i.e. StoredProcedure and RowMapper class.

I am able to execute the stored procedure but after the execute method call Spring is not calling my RowMapper method MapRow.

Below is my code

DriverManagerDataSource ds = getDataSource();

        Map<String, Integer> inputValues = new HashMap<String, Integer>();
        inputValues.put("P_CLIENT_ID", java.sql.Types.VARCHAR);
        inputValues.put("P_REQ_TYP", java.sql.Types.VARCHAR);

        Map<String, RowMapper> outputMappers = new HashMap<String, RowMapper>();
        outputMappers.put("p_recordset", new SessionMgmtMapper());

        Map<String, Integer> outputValues = new HashMap<String, Integer>();
        outputValues.put("P_STATUS_CD", java.sql.Types.VARCHAR);

        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put("P_CLIENT_ID", "0c1cab610a4445929932c09efe10225a");
        valueMap.put("P_REQ_TYP", "Authorization");

MultiMapperIOStoredProc multiMapperIOStoredProc = new MultiMapperIOStoredProc(ds, "GET_CLIENT_RS1", inputValues, outputValues, outputMappers);

multiMapperIOStoredProc.executeStoredProc(valueMap);

and my MultiMapperIOStoredProc constructor.

public MultiMapperIOStoredProc(final DataSource dataSource, final String storedProc,
            final Map<String, Integer> inputValues, final Map<String, Integer> outputValues,
            final Map<String, RowMapper> outputMappers) {
        super(dataSource, storedProc);

        if (null != inputValues && inputValues.size() > 0) {
            for (final String key : inputValues.keySet()) {
                this.declareParameter(new SqlParameter(key, inputValues.get(key)));
            }
        }
     // Pass multiple Mappers
        if (null != outputMappers && outputMappers.size() > 0) {
            for (final String key : outputMappers.keySet()) {
                this.declareParameter(new SqlOutParameter(key, OracleTypes.CURSOR, outputMappers.get(key)));
            }
        }

        if (null != outputValues && outputValues.size() > 0) {
            for (final String key : outputValues.keySet()) {
                this.declareParameter(new SqlOutParameter(key, outputValues.get(key)));
            }
        }

        this.compile();
    }

My executeStoredProc method

  public <T> List<T> executeStoredProc(final Map<String, Object> valueMap) {

        LOG.debug("executing stored procedure " + this.getSql() + " with values: " + valueMap);

        // execute stored procedure
        final Map<String, Object> resultMap = this.execute(valueMap);
        return null;
    }

Any idea how to make this work.

Answer 1

So this works with a little change. As per the Spring doc we have to pass the IN and OUT parameters in the order they are defined in the underlying Stored Procedure in Oracle DB But if you look at the above code I was using a HashMap implementation of Map to set my IN and OUT parameters which does NOT give any ordering guaranty as per Java Doc.

I changed all the Map implementation from HashMap to LinkedHashMap as below.

// For IN params.
LinkedHashMap<String, Integer> inputValues = new LinkedHashMap<String, Integer>();

// for output values
Map<String, RowMapper> outputMappers = new LinkedHashMap<String, RowMapper>();

// For OUT params.
LinkedHashMap<String, Integer> outputValues = new LinkedHashMap<String, Integer>();

The Spring documentation also shows a use of Map which is incorrect and needs to change to any ordered data structure.

This resolved my issue. Happy coding.