| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| Help Calling Oracle stored procedure ref cursor in esql |
« View previous topic :: View next topic » |
| Author |
Message
|
| JosephGramig |
 Posted: Wed Jul 03, 2013 12:34 pm Post subject: |
 |
|
Grand Master
Joined: 09 Feb 2006
Posts: 1244
Location: Gold Coast of Florida, USA
|
And did you call it with:
| Code: |
| CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE[],cursor.P_SUCCESS_FLAG[]); |
? |
|
| Back to top |
|
 |
| kash3338 |
| Posted: Wed Jul 03, 2013 5:38 pm Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| karthusb wrote: |
| Code: |
| CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE[],cursor.P_SUCCESS_FLAG[]); |
| Code: |
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDNN DECIMAL ,IN P_OLD_METER_NUMBER CHARACTER , OUT P_SITE_INFO CHARACTER , OUT P_SUCCESS_FLAG CHARACTER , OUT P_ERROR_MESSAGE CHARACTER)
LANGUAGE DATABASE
--DYNAMIC RESULT SETS 3
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO";
|
|
Its really getting confused here. The above was the code you gave us recently. The above code is incorrect. The parameters that you have in the CALL statement does not match that of your ESQL DATABASE procedure.
Until you give us the exact code that you have now (with all the suggestions here), it would be tough to actually know the problem.
As for your DB Stored Procedure,
| Code: |
PROCEDURE get_site_info
(
p_ev_idn IN events.EV_IDN%type,
p_old_meter_number IN installed_registers.insr_meter_number%type,
p_tms_code IN installed_registers.insr_tms_code%type,
p_site_info OUT site_cur,
p_success_flag OUT varchar2, -- Y - Success, N - Fail
p_error_message OUT varchar2
);
|
I can see, you have 3 IN parameters, 1 Dynamic result Set and 2 OUT parameters. But you do not have this signature in your ESQL DATABASE PROCEDURE. |
|
| Back to top |
|
|
| karthusb |
| Posted: Thu Jul 04, 2013 3:04 pm Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Yes the statement you have said is true .
The DB procedure returns 1 result set and 2 out parameters considered to be totally 3 out parameters .
following are the ways i have tried to retrieve details from esql
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN DECIMAL ,IN P_OLD_METER_NUMBER CHARACTER , OUT P_SUCCESS_FLAG CHARACTER , OUT P_ERROR_MESSAGE CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO";
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE,cursor.P_SUCCESS_FLAG);
======
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN DECIMAL ,IN P_OLD_METER_NUMBER CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO";
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE[],cursor.P_SUCCESS_FLAG[]);
======
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN DECIMAL ,IN P_OLD_METER_NUMBER CHARACTER ,OUT P_SITE_INFO[] CHARACTER, OUT P_SUCCESS_FLAG[] CHARACTER , OUT P_ERROR_MESSAGE[] CHARACTER)
LANGUAGE DATABASE
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO";
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE[],cursor.P_SUCCESS_FLAG[]);
none of the above code is working calling SP .
I knew esql Database procedure was written wrong calling dynamic resultset . please suggest the correction . |
|
| Back to top |
|
|
| karthusb |
| Posted: Mon Jul 08, 2013 5:21 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Hi Can you please reply to where my code is wrong , so that i can give a try.
Thanks,
Sri |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jul 08, 2013 5:34 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Given this definition that you provided:
| Code: |
PROCEDURE get_site_info
(
p_ev_idn IN events.EV_IDN%type,
p_old_meter_number IN installed_registers.insr_meter_number%type,
p_tms_code IN installed_registers.insr_tms_code%type,
p_site_info OUT site_cur,
p_success_flag OUT varchar2, -- Y - Success, N - Fail
p_error_message OUT varchar2
); |
This seems like the best match ESQL signature that you also provided:
| Code: |
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN DECIMAL ,IN P_OLD_METER_NUMBER CHARACTER , OUT P_SUCCESS_FLAG CHARACTER , OUT P_ERROR_MESSAGE CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO"; |
However, your CALL was wrong as the result sets should be placed last in the CALL, like this:
| Code: |
| CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_ERROR_MESSAGE,cursor.P_SUCCESS_FLAG,cursor.P_SITE_INFO[]); |
If this still fails, post the full error message as we cannot see problems without the errors. Also check that your ODBC definition has the "ProcedureReturnsResultSets" flag set.
In addition, you could try with a simpler test procedure, get that working and then work out what is wrong with the one you are trying to call through elimination and iteration.
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 |
|
|
| karthusb |
| Posted: Mon Jul 08, 2013 6:26 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
I am sorry , I tried with your sugeestion stil no success.
from the below error messge its clearly seen that error occured in ESQL Procedure .
I am not sure what combination i need to add to it..
Complete Error Messge :
ExceptionList
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line:INTEGER:1073
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmWSInputNode
Name:CHARACTER:CSBDatabaseCall#FCMComposite_1_1
Label:CHARACTER:CSBDatabaseCall.HTTP Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbComputeNode.cpp
Line:INTEGER:489
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:CSBDatabaseCall#FCMComposite_1_4
Label:CHARACTER:CSBDatabaseCall.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:641
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:CSBDatabaseCall#FCMComposite_1_4
Label:CHARACTER:CSBDatabaseCall.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.CSBDatabaseCall_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:14.3
Insert
Type:INTEGER:5
Text:CHARACTER:GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER, cursor.P_SUCCESS_FLAG, cursor.P_ERROR_MESSAGE, cursor.P_SITE_INFO[ ]);
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line:INTEGER:767
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:CSBDatabaseCall#FCMComposite_1_4
Label:CHARACTER:CSBDatabaseCall.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:GET_SITE_INFO
RecoverableException
File:CHARACTER:F:\build\S700_P\src\DataFlowEngine\ImbDatabaseManager.cpp
Line:INTEGER:2544
Function:CHARACTER:ImbDatabaseManager::getDBProcedureParameterInformation
Type:CHARACTER:ComIbmDatabaseConnectionManager
Name:CHARACTER:ComIbmDatabaseConnectionManager
Label:CHARACTER:ComIbmDatabaseConnectionManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2921
Text:CHARACTER:The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
Insert
Type:INTEGER:5
Text:CHARACTER:CSBDSN.CSB.CSBKD400.GET_SITE_INFO
Insert
Type:INTEGER:2
Text:CHARACTER:4 |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jul 08, 2013 7:04 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If you let the error go back to the Input node it is written out to the Event Log (windows) or Syslog (other platforms) in which you get a much better description of the error (try it).
The error above tells you that your procedure ESQL procedure GET_SITE_INFO with 4 parameters (+ one result set in this case) could not be matched with a procedure in the DB. I just counted your "PROCEDURE get_site_info" to save you the trouble, and discovered that it has 5 parameters + 1 result set (assuming site_cur is the only result set). So you need to go back and check each DB parameter has a matching ESQL. In this case it looks like you need an extra parameter. Like I said above starting with a simpler procedure and working up, adding new parameters for each test would probably find you an answer much faster...
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 |
|
|
| karthusb |
| Posted: Mon Jul 08, 2013 7:40 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
| Thanks for the quick reply . I will give a try |
|
| Back to top |
|
|
|
|
|
|
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
|
|
|
|
