| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Oracle stored procedure with dynamic result set |
« View previous topic :: View next topic » |
| Author |
Message
|
| ivansla |
 Posted: Mon Jul 18, 2016 7:46 am Post subject: Oracle stored procedure with dynamic result set |
 |
|
Newbie
Joined: 18 Jul 2016
Posts: 5
|
Hi guys, I'm having a problem with calling oracle stored procedure. I'm getting this error:
| Code: |
():Text = The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
():Insert = null
():Type = 5
():Text = GOLWO_DB.UB00087.GOLWO_P_0002.WF_EVENT
():Insert = null
():Type = 2
():Text = 3
|
Definition of stored procedure is like this:
| Code: |
procedure wf_event(
p_object_key t_object_key,
--
p_case_id out varchar2,
p_event_code out varchar2,
p_attributes out t_attributes
);
type r_attributes is record (
attribute_name varchar2(50),
attr_type varchar2(1),
attr_cvalue varchar2(2000),
attr_nvalue number,
attr_dvalue date
);
type t_attributes is table of r_attributes index by PLS_INTEGER;
|
I'm calling this procedure like this:
| Code: |
CREATE PROCEDURE wf_event(IN objectKey CHARACTER, OUT case_id CHARACTER, OUT event_code CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "UB00087.golwo_p_0002.wf_event";
DECLARE case_id CHARACTER;
DECLARE event_code CHARACTER;
call wf_event('Z0000000000006587', case_id, event_code, Environment.Variables.golsp[]);
|
I have procedureReturnsResults enabled in ODBC.
I'm working on integration bus 10.
The code is inside database node.
Could someone tell me what am I doing wrong? I read so many posts on internet regarding this problem but nothing helps. Thanks for all your help. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Mon Jul 18, 2016 8:50 am Post subject: Re: Oracle stored procedure with dynamic result set |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| ivansla wrote: |
Hi guys, I'm having a problem with calling oracle stored procedure. I'm getting this error:
| Code: |
():Text = The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.
():Insert = null
():Type = 5
():Text = GOLWO_DB.UB00087.GOLWO_P_0002.WF_EVENT
():Insert = null
():Type = 2
():Text = 3
|
|
So which part of the above do you not understand?
Verify the signature of your procedure definition in ESQL. It must be an exact match to the definition in the DB. Looks like you are missing the attributes parameter.... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| ivansla |
| Posted: Mon Jul 18, 2016 12:30 pm Post subject: |
|
|
Newbie
Joined: 18 Jul 2016
Posts: 5
|
Hi thanks for your reply.
Which parameter do you think I'm missing?
Procedure signiture has 1 IN and 3 OUTs (1 of these OUTs is dynamic reference) that is 4 parameters in total.
| Code: |
| CREATE PROCEDURE wf_event(IN objectKey CHARACTER, OUT case_id CHARACTER, OUT event_code CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "UB00087.golwo_p_0002.wf_event"; |
Procedure has 1 IN, 2 scalar OUTs and 1 dynamic result set.
| Code: |
| call wf_event('Z0000000000006587', case_id, event_code, Environment.Variables.golsp[]); |
IN = 'Z0000000000006587'
OUT1 = case_id
OUT2 = event_code
OUT3(dynamic result set) = Environment.Variables.golsp[]
| Code: |
procedure wf_event(
p_object_key t_object_key,
--
p_case_id out varchar2,
p_event_code out varchar2,
p_attributes out t_attributes
); |
Signiture in DB has 1 IN and 3 OUTs |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jul 18, 2016 12:45 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
You might want to look into details what is understood as a dynamic result set.
Obviously the ESQL syntax does not think so, or you would not be getting the error you are getting.
Also make sure you have not a procedure with the same name with an overloaded signature on the DB. (not allowed)
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| ivansla |
| Posted: Tue Jul 19, 2016 1:11 am Post subject: |
|
|
Newbie
Joined: 18 Jul 2016
Posts: 5
|
Hi, I'm starting to think the problem is in the procedure signature itself.
| Code: |
procedure wf_event(
p_object_key t_object_key,
--
p_case_id out varchar2,
p_event_code out varchar2,
p_attributes out t_attributes
);
type r_attributes is record (
attribute_name varchar2(50),
attr_type varchar2(1),
attr_cvalue varchar2(2000),
attr_nvalue number,
attr_dvalue date
);
type t_attributes is table of r_attributes index by PLS_INTEGER; |
This IBM documentation https://www.ibm.com/support/knowledgecenter/SSKM8N_8.0.0/com.ibm.etools.mft.doc/ak04970_.htm states that
| Quote: |
All external procedures have the following restrictions:
A stored procedure cannot be overloaded on the database side. A stored procedure is considered overloaded if there is more than one procedure of the same name in the same database schema. If the broker detects that a procedure has been overloaded, it raises an exception.
Parameters cannot be of the ESQL REFERENCE, ROW, LIST, or INTERVAL data types.
User-defined types cannot be used as parameters or as return values. |
Am I understanding correctly that defining type as table of records and type as record is considered as User-defined type?
If that's so, is it possible to perform workaround by using PASSTHRU to call this procedure? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jul 19, 2016 4:14 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
You can try pass through. You could also try and store the structure as fields in a temporary table. The result would then be a select on the temporary table so you would not have a structure...
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
