ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportProcedure Call Error

Post new topicReply to topic
Procedure Call Error View previous topic :: View next topic
Author Message
sukhendu650
PostPosted: Wed May 09, 2018 12:28 am Post subject: Procedure Call Error Reply with quote

Newbie

Joined: 08 May 2018
Posts: 5

Hi All,
I am getting error while invoking the procedure sampleProc.I feel that the out parameter in call statement is giving the error but could not find any suitable alternative.

***PROCEDURE***

create or replace
PROCEDURE sampleProc (
xyz IN NUMBER,
image IN NUMBER,
RC1 OUT Omwb_emulation.globalPkg.RCT1)
AS
BEGIN
open RC1 for
select ACCT_GROUP from ACCOUNTIMAGES
where ACCT_NUM = xyz
and IMAGE_ID = image;
END sampleProc;


******PROCEDURE CALL******

DECLARE RESULTSET ROW;

CALL sampleProc(30000,100,RESULTSET.ResultSet1[]);

CREATE PROCEDURE sampleProc(IN nAcct_Num INTEGER, IN nIMGID INTEGER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "sampleProc";




Please help!thanks In advance
Back to top
View user's profile Send private message
sukhendu650
PostPosted: Wed May 09, 2018 1:39 am Post subject: Reply with quote

Newbie

Joined: 08 May 2018
Posts: 5

below is the error
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<SOAP-ENV:Fault>
<faultcode>SOAP-ENV:Server</faultcode>
<faultstring>BIP3113E: Exception detected in message flow demo (integration node xyq)</faultstring>
<faultactor>http://localhost:7080/demo</faultactor>
<detail>
<text>Exception. BIP2230E: Error detected whilst processing a message in node 'demo.Compute'. : F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp: 515: ImbComputeNode::evaluate: ComIbmComputeNode: demo#FCMComposite_1_3
BIP2488E: ('.demo_Compute.Main', '6.3') Error detected whilst executing the SQL statement ''CALL sampleProc(3000, 1, RESULTSET.resultset[]);''. : F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp: 792: SqlStatementGroup::execute: :
BIP2934E: Error detected whilst executing the function or procedure ''sampleProc''. : F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp: 1451: SqlRoutine::invoke: :
BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''. : F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp: 3684: ImbOdbcStatement::checkRcInner: :
BIP2322E: Database error: SQL State ''HY000''; Native Error Code '6550'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'sampleProc' ORA-06550: line 1, column 8: PL/SQL: Statement ignored''. : F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp: 3899: ImbOdbcStatement::checkRcInner: :</text>
</detail>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Back to top
View user's profile Send private message
exerk
PostPosted: Wed May 09, 2018 1:50 am Post subject: Reply with quote

Jedi Council

Joined: 02 Nov 2006
Posts: 5898

And this is General Forums Information how?

Please take more care when posting, and please choose a more appropriate forum next time - moving it to the Broker forum.
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys.

Back to top
View user's profile Send private message
fjb_saper
PostPosted: Wed May 09, 2018 2:16 am Post subject: Reply with quote

Grand Poobah

Joined: 18 Nov 2003
Posts: 19725
Location: LI,NY

Quote:
wrong number or types of arguments in call to 'sampleProc' ORA-06550: line 1, column 8: PL/SQL: Statement ignored

And here I thought the error was quite clear.

Remember you cannot define a call against a procedure with multiple signatures....
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
sukhendu650
PostPosted: Wed May 09, 2018 2:55 am Post subject: Reply with quote

Newbie

Joined: 08 May 2018
Posts: 5

Sorry can you elaborate on this

Remember you cannot define a call against a procedure with multiple signatures.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Wed May 09, 2018 4:40 am Post subject: Reply with quote

Disciple

Joined: 17 Jul 2017
Posts: 196
Location: UK

Hi...does your odbc settings support result sets, i.e has it got 'ProcedureRetResults=1' set at the Datasource stanza?

Also the RESULTSET used at 'RESULTSET.ResultSet1[]' is this a valid reference, try using a known one, eg 'Environment.ResultSet1[]' and see that helps
Back to top
View user's profile Send private message
sukhendu650
PostPosted: Wed May 09, 2018 5:02 am Post subject: Reply with quote

Newbie

Joined: 08 May 2018
Posts: 5

abhi_thri wrote:
Hi...does your odbc settings support result sets, i.e has it got 'ProcedureRetResults=1' set at the Datasource stanza?

Also the RESULTSET used at 'RESULTSET.ResultSet1[]' is this a valid reference, try using a known one, eg 'Environment.ResultSet1[]' and see that helps


Yes odbc settings supports result sets, and I have also tried known reference that u mentioned...
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Wed May 09, 2018 5:47 am Post subject: Reply with quote

Disciple

Joined: 17 Jul 2017
Posts: 196
Location: UK

The database stored procedure parameter RC1, is it a used defined type or normal cursor

As per infocenter user-defined parameters are not supported 'User-defined types cannot be used as parameters or as return values'

https://www.ibm.com/support/knowledgecenter/en/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04970_.htm

See a similar topic discussing the same,

http://www.mqseries.net/phpBB2/viewtopic.php?t=72646&sid=278e197507bd9aa75d63af11e12bf001
Back to top
View user's profile Send private message
sukhendu650
PostPosted: Wed May 09, 2018 9:46 pm Post subject: Reply with quote

Newbie

Joined: 08 May 2018
Posts: 5

abhi_thri wrote:
The database stored procedure parameter RC1, is it a used defined type or normal cursor

As per infocenter user-defined parameters are not supported 'User-defined types cannot be used as parameters or as return values'

https://www.ibm.com/support/knowledgecenter/en/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04970_.htm

See a similar topic discussing the same,

http://www.mqseries.net/phpBB2/viewtopic.php?t=72646&sid=278e197507bd9aa75d63af11e12bf001


its not a user defined cursor.
Providing similar procedure which worked with similar call statement,if you could find out some difference between the 2 procedures.


create or replace
PROCEDURE "get" (
Acct_Num IN NUMBER DEFAULT NULL,
Image_Type IN VARCHAR2 DEFAULT NULL,
RC1 OUT Omwb_emulation.globalPkg.RCT1)
AS
in_Acct_Num NUMBER(19,4);
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
/******************************************************************************
$Revision: 1.1 $
******************************************************************************/
BEGIN
get.in_Acct_Num := get.Acct_Num;
OPEN RC1 FOR
SELECT AccountImages.BRANCHNO,AccountImages.image_id image_id
, image_binary image_binary
, description description
, Date_Exp
FROM AccountImages,Images
WHERE AccountImages.acct_num = get.in_Acct_Num
AND Images.image_type = get.Image_Type
AND AccountImages.image_id = Images.image_id
--AND (NVL(AccountImages.status,'0') <>'D')
--AND AccountImages.Auth_Stat = 'A'
AND decode(AccountImages.status,'D',
decode(AccountImages.Auth_Stat,'A','0','N','1','2'),
NULL,
decode(AccountImages.Auth_Stat,'A','1','N','0','2'),'0')='1'
ORDER BY AccountImages.Date_Crtd DESC ;


END get;
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportProcedure Call Error
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.