title: How to use Spring NamedParameterJdbcTemplate with an Oracle Stored Procedure with Cursor Out Parameters
body: <p>I'm trying to use Springs's NamedParameterJdbcTemplate to call an Oracle stored procedure that has two input parameters with set values and the stored procedure returns 8 cursors with multiple columns/rows. I was able to call the stored procedure using Spring's SimpleJdbcCall using the following code (sensitive information has been changed):</p>
  
  <pre><code>    SqlParameter con_num =  new SqlParameter("con_num",12); //12 is VARCHAR TYPE
      SqlParameterValue con_num2 = new SqlParameterValue(con_num, "859999"); 
      SqlParameter in_date = new SqlParameter("in_date",12);
      SqlParameterValue in_date2 = new SqlParameterValue(in_date, "20160628"); 
      SqlOutParameter account_cur = new SqlOutParameter("account_cur", -10); //-10 is ORACLE CURSOR TYPE
      SqlOutParameter fund_cur = new SqlOutParameter("fund_cur", -10);
      SqlOutParameter notes_cur = new SqlOutParameter("notes_cur", -10);
      SqlOutParameter withdrawal_cur = new SqlOutParameter("withdrawal_cur", -10);
      SqlOutParameter contract_cur = new SqlOutParameter("contract_cur", -10);
      SqlOutParameter pin_cur = new SqlOutParameter("pin_cur",-10);
      SqlOutParameter alert_cur = new SqlOutParameter("alert_cur", -10);
      SqlOutParameter rider_cur = new SqlOutParameter("rider_cur",-10);   
  
  ColumnMapRowMapper [] columnMapRowMapperArray = new ColumnMapRowMapper[8];
  
  for(int x =0; x&lt;=7; x++)
  {
      columnMapRowMapperArray[x] = new ColumnMapRowMapper();  
  }
  
  SimpleJdbcCall sp = new SimpleJdbcCall(dataSource)
      .withSchemaName("SCHEMA")
      .withCatalogName("CATALOG")
      .withProcedureName("StoredProc1")   
      .withoutProcedureColumnMetaDataAccess()
      .returningResultSet("account_cur", columnMapRowMapperArray[0])
      .returningResultSet("fund_cur", columnMapRowMapperArray[1])
      .returningResultSet("notes_cur", columnMapRowMapperArray[2])
      .returningResultSet("withdrawal_cur", columnMapRowMapperArray[3])
      .returningResultSet("contract_cur", columnMapRowMapperArray[4])
      .returningResultSet("pin_cur", columnMapRowMapperArray[5])
      .returningResultSet("alert_cur", columnMapRowMapperArray[6])
      .returningResultSet("rider_cur", columnMapRowMapperArray[7])
      .declareParameters(con_num2, in_date2,account_cur,fund_cur,
      notes_cur,withdrawal_cur,contract_cur,
      pin_cur,alert_cur,rider_cur);
  </code></pre>
  
  <p>But I can't figure out how to call it using NamedParamaterJDBC... I keep getting </p>
  
  <blockquote>
    <p>SQL state [99999]; error code [17004];<br>
    Invalid column type;<br>
    Nested exception is java.sql.SQLException: Invalid column type</p>
  </blockquote>
  
  <pre><code>    String storedProc = "call SCHEMA.CATALOG.STOREDPROC1(:CON_NUM,:IN_DATE,ACCOUNT_CUR=&gt;:ACCOUNT_CUR,FUND_CUR=&gt;:FUND_CUR,NOTES_CUR=&gt;:NOTES_CUR,WITHDRAWAL_CUR=&gt;:WITHDRAWAL_CUR,CONTRACT_CUR=&gt;:CONTRACT_CUR,PIN_CUR=&gt;:PIN_CUR,ALERT_CUR=&gt;:ALERT_CUR,RIDER_CUR=&gt;:RIDER_CUR";        
  
      ColumnMapRowMapper[] columnMapRowMapperArray = new ColumnMapRowMapper[8];
      for (int x = 0; x &lt;= 7; x++) {
          columnMapRowMapperArray[x] = new ColumnMapRowMapper();
      }
      MapSqlParameterSource params = new MapSqlParameterSource();
  
      params.addValue("CON_NUM", "859999",12)
            .addValue("IN_DATE","20160628",12)
            .addValue("ACCOUNT_CUR", columnMapRowMapperArray[0],-10)
            .addValue("FUND_CUR",columnMapRowMapperArray[1],-10)
            .addValue("NOTES_CUR",columnMapRowMapperArray[2],-10 )
            .addValue("WITHDRAWAL_CUR",columnMapRowMapperArray[3],-10)
            .addValue("CONTRACT_CUR",columnMapRowMapperArray[4],-10)
            .addValue("PIN_CUR",columnMapRowMapperArray[5],-10)
            .addValue("ALERT_CUR",columnMapRowMapperArray[6],-10)
            .addValue("RIDER_CUR",columnMapRowMapperArray[7],-10);
  
      NamedParameterJdbcTemplate npt = new NamedParameterJdbcTemplate(dataSource);
  
      List&lt;Map&lt;String, Object&gt;&gt; result = npt.queryForList(storedProc, params);
  </code></pre>
  
  <p>Any help would be greatly appreciated!</p>