Calling oracle stored procedure using spring stored procedure

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.

Here is my Oracle(9i) package specification:

procedure sp_atm_withdrawal(p_catermid IN VARCHAR2,
p_start_date IN VARCHAR2,p_end_date IN VARCHAR2,p_out out sys_refcursor);

Below is the package body:

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');
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<>'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;

Here is a snippet of my Procedure Class

public class AtmStoredProcedures extends StoredProcedure {
public AtmStoredProcedures(JdbcTemplate jdbcTemplate, String 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));
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;

And Below is my DAO Implementation method

public List<Atm> loadWithdrawal(String branch, String startDate, String endDate) {
if (this.jdbcTemplate == null) {
System.out.print("JDBC TEMPLATE IS NULL");
List<Atm> withdrawals = null;
AtmStoredProcedures st = new  AtmStoredProcedures(jdbcTemplate,"pkg_data_load.sp_atm_withdrawal");
Map results = st.getCashWithdrawals(branch, startDate, endDate);
withdrawals = (List<Atm>) results.get("sys_refcursor");
} catch (DataAccessException ex) {
return withdrawals;

After successful compliation i get the error below at runtime

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


Your calls to declareParameter() must be in the same order as the Oracle stored procedure. Try this:

declareParameter(new SqlParameter("branch", Types.VARCHAR)); 
declareParameter(new SqlParameter("startDate", Types.VARCHAR)); 
declareParameter(new SqlParameter("endDate", Types.VARCHAR)); 
declareParameter(new SqlOutParameter("sys_refcursor",OracleTypes.CURSOR, rowMapper)); 

create a bean for datasource configuration:
                <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
                <property name="locations"><list>
                <property name="ignoreResourceNotFound" value="true" />
                <property name="ignoreUnresolvablePlaceholders" value="true" />

                    <bean id="defaultDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
                        <property name="driverClassName" value="${jdbc.driverClassName}"/>
                        <property name="url" value="${jdbc.url}"/>
                        <property name="username" value="${jdbc.username}"/>
                        <property name="password" value="${jdbc.password}"/>
                        <property name="accessToUnderlyingConnectionAllowed" value="true"/>
Create a dao with reference to which you inject your datasource (applicationContext.xml):
            <bean id="testDao" class="com.test.dao.testDao">
            <property name="dataSource" ref="dataSource" />
Then create a class for convert Oracle Array to java List
    public class OracleLists implements SqlReturnType {
    public Object getTypeValue(CallableStatement cs, int paramIndex,int sqlType, String typeName) throws SQLException {    
    Object[] structs = (Object[]) ((ARRAY)cs.getObject(paramIndex)).getArray(); 
    List<String> po_list = new ArrayList<String>();
            for(int i=0;i<structs.length ;i++){
           return po_list;
Then create TestDao to call procedure::
public class TestDao  {
private SimpleJdbcCall simpleJdbcCall;
public void setSimpleJdbcCall(SimpleJdbcCall simpleJdbcCall;) {
        this.simpleJdbcCall = simpleJdbcCall;

public List<String> getTestParameter(Object object) {

MapSqlParameterSource in = new MapSqlParameterSource();
        in.addValue("IN_PARAM1", object.getInParam1);

        in.addValue("OUT_PARAM1", null,OracleTypes.ARRAY, object.getOutParam1);

Map<String, Object> result = simpleJdbcCall.execute(in);
        List<String> stringList = (List<String>) result.get("OUT_PARAM1");
        return stringList ;

simpleJdbcCall= new SimpleJdbcCall(dataSource)
                        new SqlParameter("IN_PARAM",

                        new SqlInOutParameter(
                                new OracleLists()),


Need Your Help

pip3 says “resolve pkg_resources.DistributionNotFound: pip==1.3.1”

python osx python-3.x pip setuptools

On OSX 10.7.5, I'm trying to use the pip3 command to install packages to python3. When I try, I get this error message:

Create virtual directory on blob storage VHD using WebRole

visual-studio-2010 iis-7 azure virtual-directory vhd

I am interested to create virtual directories on blob storage VHD. First I consider the Hosted Web Core Web Role (, but the author suggested that we should ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.