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 Index » WebSphere Message Broker (ACE) Support » Calling Oracle Stored Procedure to return 1 resultset.

Post new topic  Reply 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

Apprentice

Joined: 09 Feb 2017
Posts: 28
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: 26093
Location: Texas, 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

Apprentice

Joined: 09 Feb 2017
Posts: 28
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: 9394
Location: US: west coast, almost. Otherwise, enroute.

Moved to broker forum
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live.
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

Knight

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

Apprentice

Joined: 09 Feb 2017
Posts: 28
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
DebAdoth
PostPosted: Tue Jan 30, 2024 5:29 am    Post subject: Reply with quote

Newbie

Joined: 29 Jan 2024
Posts: 1

Can you clarify if you encountered the error only when using IIB, and what steps you took to resolve the issue with the ODBC connection?
Back to top
View user's profile Send private message Send e-mail AIM Address
gbaddeley
PostPosted: Tue Jan 30, 2024 1:28 pm    Post subject: Reply with quote

Jedi

Joined: 25 Mar 2003
Posts: 2492
Location: Melbourne, Australia

DebAdoth wrote:
Can you clarify if you encountered the error only when using IIB, and what steps you took to resolve the issue with the ODBC connection?

AI spam bot ?
_________________
Glenn
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

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