title: Spring JcbcTemplate to call Oracle Stored Proc. Spring 3.2
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here's the log section where the statement is being prepared:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is the straight JDBC code that works (sans connection details):</p>
  
  <pre><code>private static List&lt;ScheduledClass&gt; callOracleStoredProcCURSORParameter() throws SQLException {
          Connection connection = null;
          CallableStatement callableStatement = null;
          ResultSet rs = null;
          List&lt;ScheduledClass&gt; scheduledClassList = new ArrayList&lt;ScheduledClass&gt;();
  
          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;
      }
  </code></pre>
  
  <p>Here is my non-working Spring Code (note commented out section that produces same result when passing "in" to sjc.execute()):</p>
  
  <pre><code>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&lt;String, Object&gt; 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&lt;String, Object&gt; results = sjc.execute();
  
          return results;
      }
  </code></pre>
  
  <p>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.</p>