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

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

我们在项目中使用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] 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]

以下是我的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"/>

以下是调试模式中收到的一些日志文件内容：

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

存储过程如下：

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;

任何人都可以帮我解决这里可能出错的问题吗？

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?

最满意答案

根据你的procedure ZSPPQINSERTUSERIDBA它只是没有任何返回的过程。这就是为什么你的<int-jdbc:returning-resultset name="rowMapper">就像一个错误的额外参数导致了这个错误。

因此，请考虑使用<int-jdbc:stored-proc-outbound-channel-adapter> - 单向完全用于void过程组件。

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.

更多推荐