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 SupportCalling Oracle Stored Procedure to return 1 resultset.

Post new topicReply to topic
Calling Oracle Stored Procedure to return 1 resultset. View previous topic :: View next topic
Author Message
akashdwolf
PostPosted: Mon Jul 09, 2018 11:14 am Post subject: Calling Oracle Stored Procedure to return 1 resultset. Reply with quote

Novice

Joined: 09 Feb 2017
Posts: 20
Location: Mumbai

Hi Friends,

I have created one oracle stored procedure which runs fine but I am facing issue when calling the same procedure from IIB 10.

Kindly refer the below details:

ESQL CODE:

CREATE COMPUTE MODULE FetchResultsetsFromProcedure
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN

DECLARE regionId INTEGER;
SET regionId= InputRoot.XMLNSC.Data.regionId ;

CALL GETREGIONS(regionId, Environment.Variables.OUTPUT[]) ;

RETURN TRUE;
END;

CREATE PROCEDURE GETREGIONS (IN regionId INTEGER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "GETREGIONS";

END MODULE;
-------------------------------------------------------------------------------------
ORACLE PROCEDURE:

create or replace PROCEDURE "GETREGIONS" (
regionId IN NUMBER ,
EMPLOYEES_C OUT SYS_REFCURSOR
) IS
BEGIN
open EMPLOYEES_C for select * from regions where region_id = regionId ;
END "GETREGIONS" ;
-------------------------------------------------------------------------------------

IIB Error Details:

ExceptionList
RecoverableException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbDataFlowNode.cpp
Line:INTEGER:1251
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmWSInputNode
Name:CHARACTER:nn#FCMComposite_1_1
Label:CHARACTER:nn.HTTP Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
Insert
Type:INTEGER:14
Text:CHARACTER:nn.HTTP Input
RecoverableException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp
Line:INTEGER:515
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:nn#FCMComposite_1_3
Label:CHARACTER:nn.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
Insert
Type:INTEGER:14
Text:CHARACTER:nn.Compute
RecoverableException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:792
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.FetchResultsetsFromProcedure.Main
Insert
Type:INTEGER:5
Text:CHARACTER:7.1
Insert
Type:INTEGER:5
Text:CHARACTER:CALL GETREGIONS(regionId, Environment.Variables.OUTPUT[]);
RecoverableException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line:INTEGER:1550
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:GETREGIONS
DatabaseException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp
Line:INTEGER:3814
Function:CHARACTER:ImbOdbcStatement::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2321
Text:CHARACTER:Root SQL exception
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:14
Text:CHARACTER:odbc32.dll
DatabaseException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp
Line:INTEGER:4035
Function:CHARACTER:ImbOdbcStatement::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:HY000
Insert
Type:INTEGER:2
Text:CHARACTER:6550
Insert
Type:INTEGER:5
Text:CHARACTER:[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'GETREGIONS' ORA-06550: line 1, column 8: PL/SQL: Statement ignored


Please provide your valueable feedback
Back to top
View user's profile Send private message
Vitor
PostPosted: Mon Jul 09, 2018 11:52 am Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25170
Location: Ohio, USA

When you say "it runs fine", do you mean from a harness other than IIB?

How certain are you that you can return a dynamic number of columns (select *) into an ESQL ROW variable type and that you can use the Environment tree as a target?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
akashdwolf
PostPosted: Mon Jul 09, 2018 12:46 pm Post subject: Reply with quote

Novice

Joined: 09 Feb 2017
Posts: 20
Location: Mumbai

Thanks Victor for the reply,

Actually I tested the oracle procedure from Oracle sql developer and it returned a resultset as expected.

And I have also tested the below code for a query which worked for a select * and I was able to save it in Environment tree:
SET Environment.Variables.OUTPUT[] =passthru('select * from regions where region_id = 1 ;' ) ;

So I am just trying to do the same thing using a stored procedure instead of a query and call the procedure from IIB
Back to top
View user's profile Send private message
bruce2359
PostPosted: Mon Jul 09, 2018 1:36 pm Post subject: Reply with quote

Poobah

Joined: 05 Jan 2008
Posts: 8128
Location: US: west coast, almost. Otherwise, enroute.

Moved to broker forum
_________________
I would tell you a UDP joke, but you might not get it.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Mon Jul 09, 2018 9:26 pm Post subject: Re: Calling Oracle Stored Procedure to return 1 resultset. Reply with quote

Master

Joined: 17 Jul 2017
Posts: 210
Location: UK

akashdwolf wrote:

CALL GETREGIONS(regionId, Environment.Variables.OUTPUT[]) ;


hi...can't remember whether the reference need to be a valid one for the above statement to work, can you either try creating field 'Environment.Variables' first before calling the stored proc OR call it using a known reference, eg: Environment.ResultSet[] and see that helps.
Back to top
View user's profile Send private message
akashdwolf
PostPosted: Tue Jul 10, 2018 1:10 am Post subject: Reply with quote

Novice

Joined: 09 Feb 2017
Posts: 20
Location: Mumbai

Thanks For reply...Actually the issue was with the ODBC connection,I had not ticked the (Procedure Returns Results) in the ODBC setting so I was getting the error but It is working now .
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 SupportCalling Oracle Stored Procedure to return 1 resultset.
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.