Spring JcbcTemplate to call Oracle Stored Proc. Spring 3.2

Question

I have some straight JDBC code using a CallableStatement working. I have been trying to convert it to Spring leveraging the DataSource, JdbcTemplate and SimpleJdbcCall. I have tried basically every tutorial, example and snippets from the Spring docs that I can find. With tweaking, all the Spring solutions yield the same result:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'P_GET_CLASS_SCHEDULE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Here's the log section where the statement is being prepared:

2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_classsched_ref_out
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_term
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_scauid
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:214 - Added declared parameter for [p_get_class_schedule]: p_pidm
2015-12-29 17:17:18 DEBUG SimpleJdbcCall:336 - JdbcCall call not compiled before execution - invoking compile
2015-12-29 17:17:18 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
2015-12-29 17:17:18 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@umadmn.umt.edu:7895:ADMNRED]
2015-12-29 17:17:21 DEBUG CallMetaDataProviderFactory:123 - Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2015-12-29 17:17:21 DEBUG CallMetaDataProvider:278 - Retrieving metadata for UPCLSCH/AP_ADMN/P_GET_CLASS_SCHEDULE
2015-12-29 17:17:22 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:304 - Compiled stored procedure. Call string is [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:282 - SqlCall for procedure [p_get_class_schedule] compiled
2015-12-29 17:17:22 DEBUG SimpleJdbcCall:385 - The following parameters are used for call {call UPCLSCH.P_GET_CLASS_SCHEDULE()} with: {}
2015-12-29 17:17:22 DEBUG JdbcTemplate:937 - Calling stored procedure [{call UPCLSCH.P_GET_CLASS_SCHEDULE()}]
2015-12-29 17:17:22 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
2015-12-29 17:17:22 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@xxxxx.xxx.xxx:7895:PRIVATE]
2015-12-29 17:17:24 DEBUG DataSourceUtils:332 - Returning JDBC Connection to DataSource

Here is the straight JDBC code that works (sans connection details):

private static List<ScheduledClass> callOracleStoredProcCURSORParameter() throws SQLException {
        Connection connection = null;
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        List<ScheduledClass> scheduledClassList = new ArrayList<ScheduledClass>();

        String getDBUSERCursorSql = "{call upclsch.p_get_class_schedule (?, ?, ?, ?)}";

        try {
            connection = getApConnection();
            callableStatement = connection.prepareCall(getDBUSERCursorSql);

            callableStatement.registerOutParameter("p_classsched_ref_out", OracleTypes.CURSOR);
            callableStatement.setString("p_term", "201570");          //term code
            callableStatement.setString("p_scauid", "rs213498");
            callableStatement.setString("p_pidm", null);

            callableStatement.executeUpdate();
            rs = (ResultSet) callableStatement.getObject("p_classsched_ref_out");

            while (rs.next()) {
                ScheduledClass sc = new ScheduledClass();
                sc.setCourseNumber(rs.getString("subject_code") + rs.getString("course_number"));
                sc.setCourseTitle(rs.getString("course_title"));
                scheduledClassList.add(sc);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scheduledClassList;
    }

Here is my non-working Spring Code (note commented out section that produces same result when passing "in" to sjc.execute()):

public void setDataSource(DataSource dataSource){
    this.jt = new JdbcTemplate(dataSource);
    jt.setResultsMapCaseInsensitive(true);
    sjc = new SimpleJdbcCall(jt)
            .withCatalogName("upclsch")
            .withProcedureName("p_get_class_schedule");
}

    public Map<String, Object> execute(String termCode, String netId){

        sjc.useInParameterNames("p_term", "p_scauid", "p_pidm")
            .declareParameters(new SqlOutParameter("p_classsched_ref_out", OracleTypes.CURSOR),
            new SqlParameter("p_term", OracleTypes.VARCHAR),
            new SqlParameter("p_scauid", OracleTypes.VARCHAR),
            new SqlParameter("p_pidm", OracleTypes.VARCHAR));

//        SqlParameterSource in = new MapSqlParameterSource()
//                .addValue("p_scauid", netId, OracleTypes.VARCHAR)
//                .addValue("p_term", termCode, OracleTypes.VARCHAR)
//                .addValue("p_classsched_ref_out", OracleTypes.CURSOR);


        Map<String, Object> results = sjc.execute();

        return results;
    }

I can't seem to get any additional info at TRACE or DEBUG level to see if my parameters are ordered incorrectly. Thus, I'm looking for assistance from anyone that has accomplished this task using this technique. I'm not looking to extend StoredProcedure, as the Spring docs recommend this for 3.2.

Answer 1

Solved: I figured it out after stepping through the Spring source. For those who may be interested, it involves declaring parameters that the procedure actually uses, then using the SqlParameterSource to hold the values mapped to the names as they were declared. Notice that I add the values to the map in the reverse order as I added the parameters. Also note that I added the: .withoutProcedureColumnMetaDataAccess() . This is important when declaring your own parameters as I have done.

public class ScheduledClassesDAO {
    private DataSource dataSource;
    private JdbcTemplate jt;
    private SimpleJdbcCall sjc;

    public void setDataSource(DataSource dataSource){
        this.jt = new JdbcTemplate(dataSource);
        jt.setResultsMapCaseInsensitive(true);
        sjc = new SimpleJdbcCall(jt)
                .withCatalogName("upclsch")
                .withProcedureName("p_get_class_schedule");
    }

    /**
     * This method is used to return scheduled classes by calling a stored-proc.
     * @param termCode   String: The term/semester for this lookup.
     * @param netId      String: The netId of the student to lookup
     * @return           Map<String, Object>
     */
    public Map<String, Object> execute(String termCode, String netId){

        sjc.useInParameterNames("p_term", "p_scauid", "p_pidm")
            .withoutProcedureColumnMetaDataAccess()
            .declareParameters(new SqlOutParameter("p_classsched_ref_out", OracleTypes.CURSOR),
                    new SqlParameter("p_term", OracleTypes.VARCHAR),
                    new SqlParameter("p_scauid", OracleTypes.VARCHAR),
                    new SqlParameter("p_pidm", OracleTypes.NUMBER));

        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("p_pidm", null)
                .addValue("p_scauid", netId)
                .addValue("p_term", termCode);


        Map<String, Object> results = sjc.execute(in);

        return results;
    }
}