首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >使用spring存储过程调用oracle存储过程

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

Stack Overflow用户
提问于 2012-09-21 22:13:11
回答 2查看 14K关注 0票数 3

我一直在尝试获取一段使用spring和带参数的oracle存储过程的代码,但发现运行起来很困难。指定的存储过程需要三个参数,但在错误中,它需要四个参数。第四个参数是要返回的游标。

下面是我的Oracle(9i)包规范:

代码语言:javascript
复制
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;

下面是包体:

代码语言:javascript
复制
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;

下面是我的Procedure类的一个片段

代码语言:javascript
复制
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;
}
}

下面是我的DAO实现方法

代码语言:javascript
复制
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;
}

在成功编译之后,我在运行时得到以下错误

代码语言:javascript
复制
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
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2012-09-21 22:44:15

对declareParameter()的调用顺序必须与Oracle存储过程的顺序相同。试试这个:

代码语言:javascript
复制
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)); 
票数 4
EN

Stack Overflow用户

发布于 2013-05-27 20:48:38

为数据源配置创建bean:

代码语言:javascript
复制
                <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>

创建一个引用注入数据源(applicationContext.xml)的to:

代码语言:javascript
复制
            <bean id="testDao" class="com.test.dao.testDao">
            <property name="dataSource" ref="dataSource" />
            </bean>

然后创建一个用于将Oracle数组转换为java列表的类

代码语言:javascript
复制
    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;
        }
    } 

然后创建TestDao调用procedure::

代码语言:javascript
复制
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()),

);
}
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/12532256

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档