使用spring存储过程调用oracle存储过程

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.

我一直试图获得一段代码，该代码使用带参数的spring和oracle存储过程，但一直发现它很难运行。指定的存储过程旨在期望三个参数，但在出现的错误中它预期为四个。第四个参数是要返回的游标。

Here is my Oracle(9i) package specification:

这是我的Oracle（9i）包规范：

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;

Below is the package body:

以下是包体：

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

And Below is my DAO Implementation method

以下是我的DAO实现方法

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;
try
{
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) {
System.out.print(ex.getMessage());
}
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

2 个解决方案

#1

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

您对declareParameter（）的调用必须与Oracle存储过程的顺序相同。尝试这个：

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

#2

create a bean for datasource configuration:

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

                    <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"/>
                    </bean>

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" />
            </bean>

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++){
                po_list.add(String.valueOf(structs[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)
                .withSchemaName("TESTSCHEMA")
                .withCatalogName("TESTPACKAGE")
                .withProcedureName("TESTPROC")
                .declareParameters(
                        new SqlParameter("IN_PARAM",
                                OracleTypes.VARCHAR,
                                "IN_PARAM"),

                        new SqlInOutParameter(
                                "OUT_PARAM1",
                                OracleTypes.ARRAY,
                                TestOracleType,
                                new OracleLists()),

);
}