MQSeries.net :: View topic - Parameters could not be match with Database stored procedure

SABCAS · Posted: Wed Apr 30, 2008 4:08 am Post subject:

Hello
I have a problem between the call of stored procedures from MessageFlow and the Stored procedures definition on Oracle Database.
I get from Oracle People the following definition:
Dies ist die Signatur der Function:
-- ------------------------------------------------------------------------
-- Return 0 : Message processed and saved to DB
-- 1 : Error
-- ------------------------------------------------------------------------
FUNCTION ins_spt_delta_msg ( err_msg OUT VARCHAR
,MsgId IN NUMBER
,Vorgang IN VARCHAR2
,VLineId IN NUMBER
,PoolType IN VARCHAR2 DEFAULT NULL
,TerminationType IN VARCHAR2 DEFAULT NULL
,UpstreamSpeed IN NUMBER DEFAULT NULL
,DownstreamSpeed IN NUMBER DEFAULT NULL
,FixedIP IN VARCHAR2 DEFAULT NULL
,DhcpSessionState IN VARCHAR2 DEFAULT NULL
,BrasName IN VARCHAR2 DEFAULT NULL
,BrasPort IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

In my ComputeNode I code like this:
CREATE COMPUTE MODULE SifUnityDBSender_CALL_STORE_PROCEDURES
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE errMsg CHARACTER;
DECLARE vorgang CHARACTER;
DECLARE msgid INTEGER;
DECLARE vlineid INTEGER;
DECLARE pooltype CHARACTER;
DECLARE terminationtype CHARACTER;
DECLARE upstreamspeed INTEGER;
DECLARE downstreamSpeed INTEGER;
DECLARE fixedip CHARACTER;
DECLARE dhcpsessionstate CHARACTER;
DECLARE brasname CHARACTER;
DECLARE brasport CHARACTER;
-- CALL CopyMessageHeaders();
CALL CopyEntireMessage();

IF OutputRoot.XML.SIFM.SubscriberNotify IS NULL THEN
THROW USER EXCEPTION CATALOG 'WMBv6' MESSAGE 2950 VALUES('Unknown Input Message');
END IF;

SET vorgang = 'na';
SET msgid = OutputRoot.XML.SIFM.SubscriberNotify.MsgId;
SET vlineid = OutputRoot.XML.SIFM.SubscriberNotify.VLineId;
SET pooltype = OutputRoot.XML.SIFM.SubscriberNotify.PoolType;
SET terminationtype = OutputRoot.XML.SIFM.SubscriberNotify.TerminationType;
SET upstreamspeed = OutputRoot.XML.SIFM.SubscriberNotify.UpstreamSpeed;
SET downstreamSpeed = OutputRoot.XML.SIFM.SubscriberNotify.DownstreamSpeed;
SET fixedip = OutputRoot.XML.SIFM.SubscriberNotify.FixedIP;
SET dhcpsessionstate = OutputRoot.XML.SIFM.SubscriberNotify.DhcpSessionState;
SET brasname = OutputRoot.XML.SIFM.SubscriberNotify.BrasName;
SET brasport = OutputRoot.XML.SIFM.SubscriberNotify.BrasPort;
CALL INS_SPT_DELTA_MSG(errMsg,
msgid,
vorgang,
vlineid,
pooltype,
terminationtype,
upstreamspeed,
downstreamSpeed,
fixedip,
dhcpsessionstate,
brasname,
brasport,
Environment.ResultSet[]);

IF (SQLCODE < 0 ) THEN
THROW USER EXCEPTION CATALOG 'WMBv6' MESSAGE 2950 VALUES('The SQL State ', SQLSTATE, SQLCODE,SQLNATIVEERROR,SQLERRORTEXT );
END IF;

IF errMsg = '1' THEN
THROW USER EXCEPTION CATALOG 'WMBv6' MESSAGE 2950 VALUES('Error occured by Message to process and save to UnityDB');
END IF;

RETURN TRUE;
END;

CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;

CREATE PROCEDURE INS_SPT_DELTA_MSG(OUT param1 CHARACTER,
IN param2 INTEGER,
IN param3 CHARACTER,
IN param4 INTEGER,
IN param5 CHARACTER,
IN param6 CHARACTER,
IN param7 INTEGER,
IN param8 INTEGER,
IN param9 CHARACTER,
IN param10 CHARACTER,
IN param11 CHARACTER,
IN param12 CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "ap_spt.p_onl_spt_dl.ins_spt_delta_msg";
END MODULE;

When I send a request I receive the following Failure:
<ERROR><SOURCE>SifUnityDBSender</SOURCE><TIME>2008-04-30 11:07:57.171988</TIME><PROPERTIES><MessageSet></MessageSet><MessageType></MessageType><MessageFormat></MessageFormat><Encoding>273</Encoding><CodedCharSetId>819</CodedCharSetId><Transactional>TRUE</Transactional><Persistence>FALSE</Persistence><CreationTime>2008-04-30 09:07:53.580</CreationTime><ExpirationTime>-1</ExpirationTime><Priority>0</Priority><ReplyIdentifier>000000000000000000000000000000000000000000000000</ReplyIdentifier><ReplyProtocol>MQ</ReplyProtocol><Topic></Topic><ContentType></ContentType></PROPERTIES><MQMD><SourceQueue>SIF.UNITYDB.RECEIVE.A</SourceQueue><Transactional>TRUE</Transactional><Encoding>273</Encoding><CodedCharSetId>819</CodedCharSetId><Format>MQSTR </Format><Version>2</Version><Report>0</Report><MsgType>8</MsgType><Expiry>-1</Expiry><Feedback>0</Feedback><Priority>0</Priority><Persistence>0</Persistence><MsgId>414d51204d512e534245313738392e4148180cf920011e16</MsgId><CorrelId>000000000000000000000000000000000000000000000000</CorrelId><BackoutCount>0</BackoutCount><ReplyToQ> </ReplyToQ><ReplyToQMgr>MQ.SBE1789.AIX.D </ReplyToQMgr><UserIdentifier>mqm </UserIdentifier><AccountingToken>0000000000000000000000000000000000000000000000000000000000000000</AccountingToken><ApplIdentityData> </ApplIdentityData><PutApplType>28</PutApplType><PutApplName>MQSeries Client for Java </PutApplName><PutDate>2008-04-30</PutDate><PutTime>09:07:53.580</PutTime><ApplOriginData> </ApplOriginData><GroupId>000000000000000000000000000000000000000000000000</GroupId><MsgSeqNumber>1</MsgSeqNumber><Offset>0</Offset><MsgFlags>0</MsgFlags><OriginalLength>-1</OriginalLength></MQMD><MSG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="I_SubscriberNotify-SIFM.xsd">
.<Header id="npa000001">
..<Requester>
...<ApplicationID bo="" application="com.swisscom.spt.unidb"/>
..</Requester>
..<BSR revision="1" function="SubscriberNotify">
...<ApplicationID bo="" application="com.swisscom.unidb.spt"/>
..</BSR>
..<Environment opCode="" language="de" testFlag="no" prefix="">
...<Timestamp>2001-12-17T09:30:47.0Z</Timestamp>
..</Environment>
..<FunctionHeader functionType="Datagramm" responseType="onExecution" priorityType="medium" returnCode="success"/>
.</Header>
.<SubscriberNotify>
..<MsgId>1</MsgId>
..<VLineId>1234567890</VLineId>
..<PoolType>pool1</PoolType>
..<TerminationType>ISP</TerminationType>
..<UpstreamSpeed>500</UpstreamSpeed>
..<DownstreamSpeed>5000</DownstreamSpeed>
..<FixedIP>85.5.85.100</FixedIP>
..<DhcpSessionState>activated</DhcpSessionState>
..<BrasName>ipc-bpa640-r-br-01</BrasName>
..<BrasPort>GE 1/1.1234</BrasPort>
.</SubscriberNotify>
</MSG><EXEPTLIST><RecoverableException><File>/build/S600_P/src/DataFlowEngine/ImbComputeNode.cpp</File><Line>464</Line><Function>ImbComputeNode::evaluate</Function><Type>ComIbmComputeNode</Type><Name>SifUnityDBSender#FCMComposite_1_4</Name><Label>SifUnityDBSender.CALL_STORE_PROCEDURES</Label><Catalog>BIPv600</Catalog><Severity>3</Severity><Number>2230</Number><Text>Caught exception and rethrowing</Text><RecoverableException><File>/build/S600_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp</File><Line>602</Line><Function>SqlStatementGroup::execute</Function><Type>ComIbmComputeNode</Type><Name>SifUnityDBSender#FCMComposite_1_4</Name><Label>SifUnityDBSender.CALL_STORE_PROCEDURES</Label><Catalog>BIPv600</Catalog><Severity>3</Severity><Number>2488</Number><Text>Error detected, rethrowing</Text><Insert><Type>5</Type><Text>.SifUnityDBSender_CALL_STORE_PROCEDURES.Main</Text></Insert><Insert><Type>5</Type><Text>34.3</Text></Insert><Insert><Type>5</Type><Text>INS_SPT_DELTA_MSG(errMsg, msgid, vorgang, vlineid, pooltype, terminationtype, upstreamspeed, downstreamSpeed, fixedip, dhcpsessionstate, brasname, brasport, Environment.ResultSet[ ]);</Text></Insert><RecoverableException><File>/build/S600_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp</File><Line>685</Line><Function>SqlRoutine::invoke</Function><Type>ComIbmComputeNode</Type><Name>SifUnityDBSender#FCMComposite_1_4</Name><Label>SifUnityDBSender.CALL_STORE_PROCEDURES</Label><Catalog>BIPv600</Catalog><Severity>3</Severity><Number>2934</Number><Text>Error occured in procedure</Text><Insert><Type>5</Type><Text>INS_SPT_DELTA_MSG</Text></Insert><RecoverableException><File>/build/S600_P/src/DataFlowEngine/ImbDatabaseManager.cpp</File><Line>2286</Line><Function>ImbDatabaseManager::getDBProcedureParameterInformation</Function><Type>ComIbmDatabaseConnectionManager</Type><Name>ComIbmDatabaseConnectionManager</Name><Label>ComIbmDatabaseConnectionManager</Label><Catalog>BIPv600</Catalog><Severity>3</Severity><Number>2921</Number><Text>The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.</Text><Insert><Type>5</Type><Text>NOVISA.AP_SPT.P_ONL_SPT_DL.INS_SPT_DELTA_MSG</Text></Insert><Insert><Type>2</Type><Text>12</Text></Insert></RecoverableException></RecoverableException></RecoverableException></RecoverableException></EXEPTLIST></ERROR>

Sombody knows where is the problem ?

Thanks a lot
Ciao
Sabato