| Author |
Message
|
| mqxplorer |
 Posted: Thu May 29, 2014 6:28 am Post subject: Calling stored proc in ESQL with parameter as type(s) |
 |
|
Master
Joined: 22 Jun 2009
Posts: 206
|
I have to call a Oracle stored proc with with parameters (one integer type and other type).. I mean
| Code: |
PROC_TEST(P_NUMBER INTEGER,
P_LOG_REQUEST T_LOG_REQUEST)
and T_LOG_REQUEST is a TYPE defined as
CREATE OR REPLACE
TYPE T_LOG_REQUEST AS OBJECT(
MSG_ID VARCHAR(32),
LOGIN_ID VARCHAR(32),
USER_ID VARCHAR(32)); |
When I call it from ESQL, how can I call? What type of datatype I need to define to pass it to call the stored proc? Can I use ROW datatype?
Thanks
mqxplorer |
|
| Back to top |
|
 |
| mqxplorer |
| Posted: Thu May 29, 2014 1:03 pm Post subject: |
|
|
Master
Joined: 22 Jun 2009
Posts: 206
|
Can anybody answer this question, please?
Thanks
mqxplorer |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu May 29, 2014 1:17 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You are not likely to get answers unless you show some evidence that you have tried to solve the problem yourself. Where have you looked for information? What have you tried? What error messages did you get when you tried?
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu May 29, 2014 1:24 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
The documentation for Stored Procedures explains that user defined types are not currently supported. One alternative would be to create a wrapper stored procedure that took the primitive types and then called the underlying SP itself. Please raise a requirement (RFE) if you would like to see this feature added to the product in a future release.
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 |
|
|
| hawk29 |
| Posted: Tue Nov 24, 2015 3:50 am Post subject: |
|
|
Newbie
Joined: 24 Nov 2015
Posts: 6
|
HI All,
we are using IIB9, MQ7.5. we have a requirement to call stored proc from the ESQL node. Stored proc expact Oracle Type variables object as IN parameters and returned Type variables objects as OUT parameters.
Our challenge is that we do not know how to cast and transform REFERENCE Type of esql to Type of oracle.
IIB support this features or not ? can anyone land your suggestion.  |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Nov 24, 2015 5:31 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You should find in the documentation a conversion table between ESQL and STANDARD SQL types.
User defined types are not supported and need to be wrapped. See mgk's post and he is the authority. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| maurito |
| Posted: Tue Nov 24, 2015 6:03 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| Even if you manage to call the procedure and it runs, you will get nothing back as you have not specified the parameters direction, so it will default to IN. |
|
| Back to top |
|
|
| hawk29 |
| Posted: Tue Nov 24, 2015 9:00 pm Post subject: |
|
|
Newbie
Joined: 24 Nov 2015
Posts: 6
|
@fjb_saper
Can we achieve the purpose by using mapping node or graphical data maps?? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Nov 24, 2015 11:00 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
What part of
| mgk wrote: |
| One alternative would be to create a wrapper stored procedure that took the primitive types and then called the underlying SP itself |
did you not understand?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| hawk29 |
| Posted: Tue Nov 24, 2015 11:37 pm Post subject: |
|
|
Newbie
Joined: 24 Nov 2015
Posts: 6
|
@fjb_saper
I have to create a wrapper stored procedure in Oracle or in IIB?? |
|
| Back to top |
|
|
| akil |
| Posted: Tue Nov 24, 2015 11:47 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
|
| Back to top |
|
|
| maurito |
| Posted: Wed Nov 25, 2015 1:14 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| hawk29 wrote: |
@fjb_saper
I have to create a wrapper stored procedure in Oracle or in IIB?? |
In Oracle. one that calls your Oracle SP, gets the TYPE parameter, and returns the individual values ( within the TYPE definition ) as OUT parameters. |
|
| Back to top |
|
|
|
|
