| Author |
Message
|
| akashdwolf |
 Posted: Mon Jul 09, 2018 11:14 am Post subject: Calling Oracle Stored Procedure to return 1 resultset. |
 |
|
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 |
|
 |
| Vitor |
| Posted: Mon Jul 09, 2018 11:52 am Post subject: |
|
|
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 |
|
|
| akashdwolf |
| Posted: Mon Jul 09, 2018 12:46 pm Post subject: |
|
|
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 |
|
|
| bruce2359 |
| Posted: Mon Jul 09, 2018 1:36 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9486
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 |
|
|
| abhi_thri |
| Posted: Mon Jul 09, 2018 9:26 pm Post subject: Re: Calling Oracle Stored Procedure to return 1 resultset. |
|
|
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 |
|
|
| akashdwolf |
| Posted: Tue Jul 10, 2018 1:10 am Post subject: |
|
|
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 |
|
|
| DebAdoth |
| Posted: Tue Jan 30, 2024 5:29 am Post subject: |
|
|
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 |
|
|
| gbaddeley |
| Posted: Tue Jan 30, 2024 1:28 pm Post subject: |
|
|
Jedi Knight
Joined: 25 Mar 2003
Posts: 2538
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 |
|
|
|
|
