| Author |
Message
|
| angelfrancis |
 Posted: Fri Nov 21, 2014 4:44 am Post subject: ESQL to accept reference cursor from oracle stored procedure |
 |
|
Newbie
Joined: 21 Nov 2014
Posts: 4
|
Hi,
I am trying to call an Oracle Stored Procedure which has reference cursor as one of its OUT parameter inside Compute node
But doing so i am getting error:
RESULT_SET_PARAM_0
Event viewer Log says:
A function or procedure was called, but the value supplied for the ''RESULT_SET_PARAM_0'' parameter was not a reference. The function or procedure expects a reference.
Functions and procedures do not allow data types other than references (for example, expressions) to be used for IN or INOUT parameters that are defined to be references.
Can someone help how to call ref cursor and get the data out of it??? 
Below is the details
ESQL:
CALL emp_paystub_proc('NKD1722',Environment.ResultSet1[],Status);
PROCEDURE:
CREATE PROCEDURE emp_paystub_proc (IN EMPID CHAR , OUT Status CHAR)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "APPS.emp_pay_period_pkg.emp_pay_period_proc";
ORACLE StoredProcedure definition:
Name: APPS.emp_pay_period_pkg.emp_pay_period_proc
PROCEDURE emp_pay_period_proc(
p_user_id in varchar2,
p_pay_period_date out sys_refcursor,
p_success out varchar2) |
|
| Back to top |
|
 |
| mgk |
| Posted: Fri Nov 21, 2014 5:14 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
ESQL expects that the dynamic result set values are at the end of the CALL. So please swapping the last two parameters you are passing to the CALL statement.
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| angelfrancis |
| Posted: Fri Nov 21, 2014 6:14 am Post subject: |
|
|
Newbie
Joined: 21 Nov 2014
Posts: 4
|
Thanks for this tip.
I executed with swapping the fileds, that error has gone but now i am recieving a new error:
Exception. BIP2230E: Error detected whilst processing a message in node 'getPaySlips.getPayslip_OracleDB'. : F:\build\slot1\S900_P\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp: 497: ImbComputeNode::evaluate: ComIbmComputeNode: getPaySlips#FCMComposite_1_3
BIP2488E: ('.getPaySlips_OracleDB.Main', '17.2') Error detected whilst executing the SQL statement ''emp_paystub_proc('NKD1722', Status, Environment.ResultSet1[ ]);''. : F:\build\slot1\S900_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp: 666: SqlStatementGroup::execute: ImbESQLManager: ImbESQLManager
BIP2934E: Error detected whilst executing the function or procedure ''emp_paystub_proc''. : F:\build\slot1\S900_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp: 1362: SqlRoutine::invoke: ImbESQLManager: ImbESQLManager
BIP2326E: Error casting '65535' hours '65535' minutes '1110' seconds to a TIME. State = '-1' ''S22007'' '0' '' : F:\build\slot1\S900_P\src\CommonServices\ImbDateTime.cpp: 2882: ImbTimeBase::ImbTimeBase: :
the actual Stored Procedure is returning REF_CURSOR like below data
PAY_PERIOD_START_DATE PAY_PERIOD_END_DATE
9/20/2009 10/3/2009
10/4/2009 10/17/2009
5/2/2010 5/15/2010
7/25/2010 8/7/2010
9/19/2010 10/2/2010
10/31/2010 11/13/2010
2/20/2011 3/5/2011
3/20/2011 4/2/2011 |
|
| Back to top |
|
|
| angelfrancis |
| Posted: Fri Nov 21, 2014 6:15 am Post subject: |
|
|
Newbie
Joined: 21 Nov 2014
Posts: 4
|
Please ignore my space in above result
The SP returns 2 columns to be precise |
|
| Back to top |
|
|
| angelfrancis |
| Posted: Mon Nov 24, 2014 12:00 am Post subject: |
|
|
Newbie
Joined: 21 Nov 2014
Posts: 4
|
| Could Somebody Please help on the same! |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Dec 01, 2014 6:32 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| The error you posted showed that you were trying to interpret a date value as a time value. |
|
| Back to top |
|
|
|
|
