存储过程出站网关无法执行有效的存储过程 [英] Stored Procedure Outbound Gateway is failing to execute a valid stored procedure

查看：76
本文介绍了存储过程出站网关无法执行有效的存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们在项目中使用 spring-integration.我正在使用 int-jdbc:stored-proc-outbound-gateway 来执行存储过程.当我使用 Spring-Integration 层命中存储过程时出现以下错误.但奇怪的是，相同的存储过程从 SQL Developer 成功执行并将所需的行插入表中.

2016-07-01 11:45:49,254 DEBUG http-0.0.0.0-8080-4 [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] 翻译 SQLException 与 SQL 状态 '65000'，错误代码 '6550', 消息 [ORA-06550:第 1 行，第 7 列:PLS-00306:调用ZSPPQINSERTUSERIDBA"时参数的数量或类型错误ORA-06550:第 1 行，第 7 列:PL/SQL:忽略语句];SQL 是 [{call ZSPPQINSERTUSERIDBA(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}] 用于任务 [CallableStatementCallback]

以下是我的int-jdbc:stored-proc-outbound-gateway:

以下是调试模式下收到的部分日志文件内容:

2016-07-01 11:45:48,505 DEBUG http-0.0.0.0-8080-4 [org.springframework.jdbc.core.simple.SimpleJdbcCall] 以下参数用于调用{call ZSPPQINSERTUSERIDBA(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} 与:{P_LEGACYLOGIN=ratna, P_ADDSECURPASSWORD=, P_SELECTEDIND=N, P_OTHERPASSWORD=, P_BUSINESSAREA=CS2XAAP_USERID=DT76747, P_DEFAULTIND=Y, P_OTHERLOGIN=, P_LASTUPDATE=2016-07-01-11.45.21.000590, P_ADDSECURLOGIN=, P_LASTUPDATEUSERID=DT76747, P_LEGACYSYSTEM_WORDLEAmi}sys,P_LASTUPDATE=2016-07-01-11.45.21.000590………………引起:org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback;错误的 SQL 语法 [{call ZSPPQINSERTUSERIDBA(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}];嵌套异常是 java.sql.SQLException: ORA-06550: line 1, column 7:PLS-00306:调用ZSPPQINSERTUSERIDBA"时参数的数量或类型错误ORA-06550:第 1 行，第 7 列:PL/SQL:忽略语句

存储过程如下:

创建或替换过程 ZSPPQINSERTUSERIDBA(types.char10 中的 P_USERID，types.char10 中的 P_BUSINESSAREA，在 types.char1 中的 P_SELECTEDIND，在 types.char1 中的 P_DEFAULTIND，P_LEGACYSYSTEM 在 types.char10 中，types.char16 中的 P_LEGACYLOGIN，types.char16 中的 P_LEGACYPASSWORD，types.char10 中的 P_OTHERLOGIN，types.char10 中的 P_OTHERPASSWORD，P_ADDSECURLOGIN 在 types.char10 中，types.char10 中的 P_ADDSECURPASSWORD，types.char10 中的 P_LASTUPDATEUSERID，types.char26 中的 P_LASTUPDATE)认证当前用户作为TOTALCNT 整数默认值 0 ;开始选择 count(*) 到 TOTALCNT来自 USERBUSINESSAREA其中 USERID = P_USERID和 BUSINESSAREA = P_BUSINESSAREA;如果(总数量 = 0)那么插入 USERBUSINESSAREA(用户名，商业区，SELECTEDIND,默认，遗留系统，旧版登录，法律密码，其他登录，其他密码，添加安全登录，添加密码，上次更新用户 ID，最后更新)价值观(P_用户ID，P_BUSINESSAREA,P_SELECTEDIND,P_DEFAULTIND,P_LEGACYSYSTEM,P_LEGACYLOGIN,P_LEGACYPASSWORD,P_OTHERLOGIN,P_OTHERPASSWORD,P_ADDSECURLOGIN,P_ADDSECURPASSWORD,P_LASTUPDATEUSERID,P_LASTUPDATE);如果结束；结束；

有人能帮我看看这里有什么问题吗?

解决方案

根据你的procedure ZSPPQINSERTUSERIDBA，它只是procedure，没有任何回报.这就是为什么您的 <int-jdbc:returning-resultset name="rowMapper"> 就像是导致该错误的错误和额外参数.

因此，请考虑使用 <int-jdbc:stored-proc-outbound-channel-adapter> - 完全用于 void 程序组件的一种方式.>

We are using spring-integration in our project. I am using int-jdbc:stored-proc-outbound-gateway to execute a stored procedure. I am getting the following error when I use Spring-Integration layer to hit the stored procedure.But strangely the same stored procedure is getting executed successfully from SQL Developer and inserting the required row into the table. 

2016-07-01 11:45:49,254 DEBUG http-0.0.0.0-8080-4 [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] Translating SQLException with SQL state '65000', error code '6550', message [ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ZSPPQINSERTUSERIDBA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
]; SQL was [{call ZSPPQINSERTUSERIDBA(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}] for task [CallableStatementCallback]

The following is my int-jdbc:stored-proc-outbound-gateway:

<int-jdbc:stored-proc-outbound-gateway
                        id="PQPutUserBAInformation-AWD-StoredProcedure2" 
                        auto-startup="true"
                        data-source="routingDataSource" 
                        stored-procedure-name="ZSPPQINSERTUSERIDBA"
                        skip-undeclared-results="true"
                        ignore-column-meta-data="true"  
                        use-payload-as-parameter-source = "false" 
                        expect-single-result="true" >

                        <int-jdbc:sql-parameter-definition name="P_USERID" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_BUSINESSAREA" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_SELECTEDIND" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_DEFAULTIND" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_LEGACYSYSTEM" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_LEGACYLOGIN" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_LEGACYPASSWORD" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_OTHERLOGIN" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_OTHERPASSWORD" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_ADDSECURLOGIN" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_ADDSECURPASSWORD" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_LASTUPDATEUSERID" direction="IN" type="VARCHAR" />
                        <int-jdbc:sql-parameter-definition name="P_LASTUPDATE" direction="IN" type="VARCHAR" />


                        <int-jdbc:parameter name="P_USERID" expression="#xpath(payload, '//CurrentUserID')" />
                        <int-jdbc:parameter name="P_BUSINESSAREA" expression="#xpath(payload, '//SelectedBusinessArea/Code')" />
                        <int-jdbc:parameter name="P_SELECTEDIND" expression="#xpath(payload, '//SelectedBusinessArea/Selected')" />
                        <int-jdbc:parameter name="P_DEFAULTIND" expression="#xpath(payload, '//SelectedBusinessArea/Default')" />
                        <int-jdbc:parameter name="P_LEGACYSYSTEM" expression="#xpath(payload, '//SelectedBusinessArea/LegacySystem')" />
                        <int-jdbc:parameter name="P_LEGACYLOGIN" expression="#xpath(payload, '//SelectedBusinessArea/LegacyLogin')" />

                        <int-jdbc:parameter name="P_LEGACYPASSWORD" expression="headers.LegacyPassword" />

                        <int-jdbc:parameter name="P_OTHERLOGIN" expression="#xpath(payload, '//SelectedBusinessArea/OtherLogin')" />

                        <int-jdbc:parameter name="P_OTHERPASSWORD" expression="headers.otherPassword" />

                        <int-jdbc:parameter name="P_ADDSECURLOGIN" expression="#xpath(payload, '//SelectedBusinessArea/AddSecurLogin')" />

                        <int-jdbc:parameter name="P_ADDSECURPASSWORD" expression="headers.addSecurPassword" />

                        <int-jdbc:parameter name="P_LASTUPDATEUSERID" expression="#xpath(payload, '//userID')" />
                        <int-jdbc:parameter name="P_LASTUPDATE" expression="#xpath(payload, '//dateTimeStamp')" />

                <int-jdbc:returning-resultset name="rowMapper" row-mapper="com.dsths.cs.awd.utils.ResultSetRowMapper"/>

The following is some of the log file content received in the debug mode:

2016-07-01 11:45:48,505 DEBUG http-0.0.0.0-8080-4 [org.springframework.jdbc.core.simple.SimpleJdbcCall] The following parameters are used for call {call ZSPPQINSERTUSERIDBA(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} with: {P_LEGACYLOGIN=ratna, P_ADDSECURPASSWORD=, P_SELECTEDIND=N, P_OTHERPASSWORD=, P_BUSINESSAREA=CS2XAA, P_USERID=DT76747, P_DEFAULTIND=Y, P_OTHERLOGIN=, P_LASTUPDATE=2016-07-01-11.45.21.000590, P_ADDSECURLOGIN=, P_LASTUPDATEUSERID=DT76747, P_LEGACYSYSTEM=Amisys, P_LEGACYPASSWORD=engan}

.............


Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call ZSPPQINSERTUSERIDBA(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ZSPPQINSERTUSERIDBA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The stored procedure is below:

create or replace procedure ZSPPQINSERTUSERIDBA
(
    P_USERID in types.char10,
    P_BUSINESSAREA in types.char10,
    P_SELECTEDIND in types.char1,
    P_DEFAULTIND in types.char1,
    P_LEGACYSYSTEM in types.char10,
    P_LEGACYLOGIN in types.char16,
    P_LEGACYPASSWORD in types.char16,
    P_OTHERLOGIN in types.char10,
    P_OTHERPASSWORD in types.char10,
    P_ADDSECURLOGIN in types.char10,
    P_ADDSECURPASSWORD in types.char10,
    P_LASTUPDATEUSERID in types.char10,
    P_LASTUPDATE in types.char26
)
authid current_user
as
    TOTALCNT INTEGER DEFAULT 0 ;

begin
    select count(*) into TOTALCNT 
    from USERBUSINESSAREA 
    where USERID = P_USERID
    and BUSINESSAREA = P_BUSINESSAREA;

    if ( TOTALCNT = 0 )
    then
        insert into USERBUSINESSAREA     
        ( 
            USERID,
            BUSINESSAREA,
            SELECTEDIND,
            DEFAULTIND,
            LEGACYSYSTEM,
            LEGACYLOGIN,
            LEGACYPASSWORD,
            OTHERLOGIN,
            OTHERPASSWORD,
            ADDSECURLOGIN,
            ADDSECURPASSWORD,
            LASTUPDATEUSERID,
            LASTUPDATE 
        ) 
        values
        (
            P_USERID,
            P_BUSINESSAREA,
            P_SELECTEDIND,
            P_DEFAULTIND,
            P_LEGACYSYSTEM,
            P_LEGACYLOGIN,
            P_LEGACYPASSWORD,
            P_OTHERLOGIN,
            P_OTHERPASSWORD,
            P_ADDSECURLOGIN,
            P_ADDSECURPASSWORD,
            P_LASTUPDATEUSERID,
            P_LASTUPDATE
        );
    end if;
end;

Can any one please help me out what could be the wrong here?

解决方案

According to your procedure ZSPPQINSERTUSERIDBA it is just procedure without any returns. That's why your <int-jdbc:returning-resultset name="rowMapper"> is like a wrong and extra argument causing that error.

So, consider to use <int-jdbc:stored-proc-outbound-channel-adapter> - one-way exactly for void procedures component.

这篇关于存储过程出站网关无法执行有效的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆