MQSeries.net :: View topic - Procedure Call Error

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Procedure Call Error

Procedure Call Error

« View previous topic :: View next topic »

Author

Message

sukhendu650

Posted: Wed May 09, 2018 12:28 am Post subject: Procedure Call Error

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

sukhendu650

Posted: Wed May 09, 2018 1:39 am Post subject:

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>

exerk

Posted: Wed May 09, 2018 1:50 am Post subject:

Jedi Council

Joined: 02 Nov 2006
Posts: 6339

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.

fjb_saper

Posted: Wed May 09, 2018 2:16 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
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

sukhendu650

Posted: Wed May 09, 2018 2:55 am Post subject:

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.

abhi_thri

Posted: Wed May 09, 2018 4:40 am Post subject:

Knight

Joined: 17 Jul 2017
Posts: 516
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

sukhendu650

Posted: Wed May 09, 2018 5:02 am Post subject:

Newbie

Joined: 08 May 2018
Posts: 5

abhi_thri wrote:

Yes odbc settings supports result sets, and I have also tried known reference that u mentioned...

abhi_thri

Posted: Wed May 09, 2018 5:47 am Post subject:

Knight

Joined: 17 Jul 2017
Posts: 516
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

sukhendu650

Posted: Wed May 09, 2018 9:46 pm Post subject:

Newbie

Joined: 08 May 2018
Posts: 5

abhi_thri wrote:

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;

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Procedure 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