title: Calling oracle stored procedure using spring stored procedure
body: <p>I have been trying to get a piece of code that uses spring and oracle stored procedure with parameters but have been finding it difficult to run. The stored procedure as specified is intended to expect three parameter but in the error it appears it expects four. The fourth parameter is a cursor to be returned.</p>
  
  <p>Here is my Oracle(9i) package specification:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE  pkg_data_load AS
  procedure sp_atm_withdrawal(p_catermid IN VARCHAR2,
  p_start_date IN VARCHAR2,p_end_date IN VARCHAR2,p_out out sys_refcursor);
  END;
  </code></pre>
  
  <p>Below is the package body:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE BODY pkg_data_load
  AS  
  procedure sp_atm_withdrawal
  (
  p_catermid IN VARCHAR2,
  p_start_date IN VARCHAR2,
  p_end_date IN VARCHAR2,
  p_out out sys_refcursor 
  ) as
  v_start_date date := to_date(p_start_date,'yyyy/mm/dd');
  v_end_date date := to_date(p_end_date,'yyyy/mm/dd');
  begin
  open p_out for select 
  b.nam_branch BRANCH_NAME
  , a.bcode brn_Code
  , a.acct_no Acct_no
  from table a, table b where b.cod_Cc_brn= a.cod_org_brn 
  and a.cod_reply=0 
  and b.flg_mnt_status='A' 
  and a.cod_proc not in ( 312000, 382000, 311000, 381000) 
  and a.cod_txn_literal&lt;&gt;'SCD' 
  and a.ca_term_id in (
  select ca_term_id from tablec where flg_mnt_status='A')
  and a.dat_post_stl between v_start_date and  v_end_date
  and a.ca_term_id = p_catermid;
  end sp_atm_withdrawal;
  END pkg_data_load;
  </code></pre>
  
  <p>Here is a snippet of my Procedure Class</p>
  
  <pre><code>public class AtmStoredProcedures extends StoredProcedure {
  public AtmStoredProcedures(JdbcTemplate jdbcTemplate, String procedure)
  {
  super(jdbcTemplate,procedure);
  AtmRowMapper rowMapper = new AtmRowMapper();
  declareParameter(new SqlOutParameter("sys_refcursor",OracleTypes.CURSOR, rowMapper));
  declareParameter(new SqlParameter("branch", Types.VARCHAR));
  declareParameter(new SqlParameter("startDate", Types.VARCHAR));
  declareParameter(new SqlParameter("endDate", Types.VARCHAR));
  compile();
  }
  public Map getCashWithdrawals(String branch, String startDate, String endDate)
  {
  Map inParam = new HashMap();
  inParam.put("branch", branch);
  inParam.put("startDate", startDate);
  inParam.put("endDate", endDate);
  Map out = execute(inParam); // Call on parent class
  return out;
  }
  }
  </code></pre>
  
  <p>And Below is my DAO Implementation method</p>
  
  <pre><code>public List&lt;Atm&gt; loadWithdrawal(String branch, String startDate, String endDate) {
  if (this.jdbcTemplate == null) {
  System.out.print("JDBC TEMPLATE IS NULL");
  }
  List&lt;Atm&gt; withdrawals = null;
  try
  {
  AtmStoredProcedures st = new  AtmStoredProcedures(jdbcTemplate,"pkg_data_load.sp_atm_withdrawal");
  Map results = st.getCashWithdrawals(branch, startDate, endDate);
  withdrawals = (List&lt;Atm&gt;) results.get("sys_refcursor");
  } catch (DataAccessException ex) {
  System.out.print(ex.getMessage());
  }
  return withdrawals;
  }
  </code></pre>
  
  <p>After successful compliation i get the error below at runtime </p>
  
  <pre><code>CallableStatementCallback; bad SQL grammar 
  [{call pkg_data_load.sp_atm_withdrawal(?, ?, ?, ?)}]; 
  nested exception is java.sql.SQLException: 
  ORA-06550: line 1, column 7: PLS-00306: wrong number or 
  types of arguments in call to 'SP_ATM_WITHDRAWAL'ORA-06550: 
  line 1, column 7: PLS-00306: wrong number or types of arguments 
  in call to 'SP_ATM_WITHDRAWAL'
  ORA-06550: line 1, column 7:
  PL/SQL: Statement ignored
  </code></pre>