| Author |
Message
|
| karthusb |
 Posted: Thu Jun 27, 2013 1:02 pm Post subject: Help Calling Oracle stored procedure ref cursor in esql |
 |
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Hello All ,
I am trying to call oracle stored procedure which has reference cursor, I tried muliple way but getting following error
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
Any one has answere please help .
below is the procedure and call from esql
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN INTEGER ,IN P_OLD_METER_NUMBER CHAR )
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO";
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,Environment.Variable.P_SITE_INFO[],Environment.Variable.P_OLD_METER_NUMBER ,Environment.Variable.P_SUCCESS_FLAG); |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Jun 27, 2013 9:05 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Welcome to this forum.
You are certainly diving in at the deep end.
However this topic has been discussed many times before here. Please take some time and use the google search (top right of the page) and look at those other posts.
IF your problem is not covered by them please come back here and add a reply to this thread saying something like
I looked at these threads
<insert list>
and none of the solutions apply.
Then we can move on to help you knowing that all the common solutions have been covered.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| karthusb |
| Posted: Tue Jul 02, 2013 9:45 am Post subject: Still no Success |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Hello Thanks for your response ,
Passthrough statement works but returns only one result set .
SET Environment.Variable.output[] = PASSTHRU('{CALL csb.csbkd400.get_site_info(?,?,?,?,?)}' values (P_EV_IDN, P_OLD_METER_NUMBER,P_SITE_INFO,P_OLD_METER_NUMBER ,P_SUCCESS_FLAG));
Changed DNS advaced setting 1) Enable SQLDescribeParams and Procedure Returns Reslults but no luck
3 out parameters from my query has to return 3 resut sets this is not happening via PASSTHRU. I dont know what datatype to pass to get the cursor results from Database .
Error with regular query .
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
Anyone has answere please reply |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jul 02, 2013 11:21 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
So did you look at previous answers to this question?
Did you configure the Oracle ODBC Connection correctly?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| karthusb |
| Posted: Tue Jul 02, 2013 11:50 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Previous answeres doesn't clearly have any resolution steps ..-(
Anyway , If the Connections are wrong I should not get PASSTHRU statement work .
Its not connection problem Its calling statement that's causing problem when referring to the reference cursor in stored proc .
If you suggest me to try differently please help. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Tue Jul 02, 2013 5:55 pm Post subject: Re: Help Calling Oracle stored procedure ref cursor in esql |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| karthusb wrote: |
| Code: |
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN INTEGER ,IN P_OLD_METER_NUMBER CHAR )
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO";
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,Environment.Variable.P_SITE_INFO[],Environment.Variable.P_OLD_METER_NUMBER ,Environment.Variable.P_SUCCESS_FLAG);
|
|
What is the datatype of "P_EV_IDN" and "P_OLD_METER_NUMBER" in your Stored Procedure? You have defined 3 dynamic result sets, but you just have one Environment variable declared as vector value, it should be 3.
What is the purpose of these two variables in your CALL statement, Environment.Variable.P_OLD_METER_NUMBER ,Environment.Variable.P_SUCCESS_FLAG? |
|
| Back to top |
|
|
| karthusb |
| Posted: Wed Jul 03, 2013 5:27 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
P_EV_IDN = NUMBER(9,0) in SP
p_OLD_METER_NUMBER = VARCHAR2(25 bytes) in SP
Out of 3 dynamic results only one returns list which is the cursor referense in SP .
other 2 returns character values.
P_EV_IDN ,p_OLD_METER_NUMBER are the IN Parameters to the SP which returns 3 dynamic results in 3 Out parameters.
Thanks for the support.. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jul 03, 2013 6:00 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| karthusb wrote: |
Out of 3 dynamic results only one returns list which is the cursor referense in SP .
other 2 returns character values.
|
This is what the Infocenter says here
| Quote: |
| If the called routine has any DYNAMIC RESULT SETS specified in its definition, the number of expressions in the CALL statement's ParameterList must match the number of parameters to the routine, plus the number of DYNAMIC RESULT SETS. For example, if the routine has three parameters and two DYNAMIC RESULT SETS, the CALL statement must pass five parameters to the called routine. The parameters passed for the two DYNAMIC RESULT SETS must be list parameters; that is, they must be field references qualified with array brackets [ ]; for example, Environment.ResultSet1[]. |
|
|
| Back to top |
|
|
| karthusb |
| Posted: Wed Jul 03, 2013 8:07 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
I tried all the combinations before posting .. but no luck ..
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDN INTEGER ,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";
DECLARE cursor REFERENCE TO Environment.Variable.output;
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE[],cursor.P_SUCCESS_FLAG[]);
Big Question is what datatype should be declared to P_SITE_INFO as it will return list ?
And as per the error
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure
first '&1' with '&2' parameters are not able to parse before resulting. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jul 03, 2013 9:11 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| karthusb wrote: |
| P_EV_IDN = NUMBER(9,0) in SP |
Here is your problem. Refer this link and you will get the problem.
Oracle NUMBER(P,S) should be DECIMAL in ESQL and not INTEGER. |
|
| Back to top |
|
|
| karthusb |
| Posted: Wed Jul 03, 2013 9:38 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Thank you for your suggestion ,
I even tried that earlier but no luck . But upon your suggestion I gave a shot once again but same error prevails. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jul 03, 2013 9:45 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| karthusb wrote: |
Thank you for your suggestion ,
I even tried that earlier but no luck . But upon your suggestion I gave a shot once again but same error prevails. |
Can you please share the ESQL DATABASE PROCEDURE along with the CALL statements and the variable declarations using [c o d e] tags?
Also, did you try with FLOAT datatype? |
|
| Back to top |
|
|
| karthusb |
| Posted: Wed Jul 03, 2013 9:57 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
Yes I did try with float , decimal Datatype earlier ..
I definetly think there is no issue with SP as its working good with PASSTHRU statement .
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
);
end CSBKD400;
events.EV_IDN%type and installed_registers.insr_meter_number%type details refers from different table
as EV_IDN ( NUMBER 9,0) , insr_meter_number ( VARCHAR2 ,25)
ESQL : Not Working
==============
DECLARE cursor REFERENCE TO Environment.Variable.output;
DECLARE P_SITE_INFO CHARACTER;
DECLARE P_SUCCESS_FLAG CHARACTER;
DECLARE P_ERROR_MESSAGE CHARACTER;
DECLARE P_EV_IDN REFERENCE TO InputRoot.XMLNSC.*:Envelope.*:Body.ms:MeterCompletionRequest.ms:EventId;
SET P_EV_IDN = CAST(P_EV_IDN AS DECIMAL);
DECLARE P_OLD_METER_NUMBER REFERENCE TO InputRoot.XMLNSC.*:Envelope.*:Body.ms:MeterCompletionRequest.ms:OutMeterNumber;
-- SET P_OLD_METER_NUMBER = CAST(P_OLD_METER_NUMBER AS DECIMAL);
-- DECLARE P_TMS_CODE CHAR;
CALL GET_SITE_INFO(P_EV_IDN, P_OLD_METER_NUMBER,cursor.P_SITE_INFO[],cursor.P_ERROR_MESSAGE[],cursor.P_SUCCESS_FLAG[]);
SET OutputRoot.XMLNSC.DBResponse.Reply.ReplyCode[] = Environment.Variable.output.P_SUCCESS_FLAG[];
SET OutputRoot.XMLNSC.DBResponse.Reply.siteinfo[] = Environment.Variable.output.P_SITE_INFO[];
SET OutputRoot.XMLNSC.DBResponse.Reply.errorMessage[] = Environment.Variable.output.P_ERROR_MESSAGE[];
RETURN TRUE;
END;
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";
ESQL : Working
problem is returning only one result set. good part is its returning cursor details
SET Environment.Variable.output[] = PASSTHRU('{CALL csb.csbkd400.get_site_info(?,?,?,?,?)}' values (P_EV_IDN, P_OLD_METER_NUMBER,P_SITE_INFO,P_OLD_METER_NUMBER ,P_SUCCESS_FLAG)); |
|
| Back to top |
|
|
| JosephGramig |
| Posted: Wed Jul 03, 2013 11:21 am Post subject: |
|
|
Grand Master
Joined: 09 Feb 2006
Posts: 1244
Location: Gold Coast of Florida, USA
|
try
| Code: |
CREATE PROCEDURE GET_SITE_INFO(IN P_EV_IDNN DECIMAL ,IN P_OLD_METER_NUMBER CHARACTER )
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "csb.csbkd400.GET_SITE_INFO"; |
That's two things you are passing in plus three RESULT SETS. Which matches your call statement. |
|
| Back to top |
|
|
| karthusb |
| Posted: Wed Jul 03, 2013 11:31 am Post subject: |
|
|
Novice
Joined: 26 Jun 2013
Posts: 12
|
| thats the first piece of code i posted when asking for help. It did not work |
|
| Back to top |
|
|
|
|
