| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| External Oracle Procedure Call |
« View previous topic :: View next topic » |
| Author |
Message
|
| anvanzyl |
 Posted: Wed May 10, 2006 8:45 pm Post subject: External Oracle Procedure Call |
 |
|
Novice
Joined: 03 Jan 2005
Posts: 19
Location: Melbourne, Australia
|
Hi,
I am trying to call the following external Oracle procedure from a WBIMB v5 (CSD4) message flow:
(schema 'ABC')
CREATE OR REPLACE PROCEDURE GET_COMP_ID (O_status OUT VARCHAR2,
O_error_message OUT VARCHAR2,
IO_array IN OUT PACK_ID_ARRAY)
...
PACK_ID_ARRAY definition: CREATE OR REPLACE TYPE PACK_ID_ARRAY IS VARRAY() OF PACK_ID_REC
PACK_ID_REC definition: CREATE OR REPLACE TYPE PACK_ID_REC AS OBJECT( PACK_ITEM VARCHAR2(25), COMP_ITEM VARCHAR2(25));
Extract from ESQL code:
CREATE
CREATE PROCEDURE SimplePackEnrich(OUT O_status char, OUT O_error_msg char, INOUT IO_SP_array char) EXTERNAL NAME "ABC.GET_COMP_ID";
CALL
call SimplePackEnrich(O_status, O_error_msg, Environment.eaiEnrich.Items[]);
Array of items stored in Environment:
SET ENVIRONMENT.eaiEnrich.Items[i].packItem = currentDetailRef.ItemIdentifier;
SET ENVIRONMENT.eaiEnrich.Items[i].compItem = '';
The procedure will access Oracle Db and check if the packItem it receives is in a specific table. If found it will return a corresponding compItem value else it will set the compItem value to the input packItem value.
I have set up the ODBC connection following the instructions from IBM documentation and tested the connection successfully. The Oracle DBA's have set up my user account with the required privaledges and I have set the Broker to run using this user ID.
What I would like to know is whether I am sending the array of items in Environment.eaiEnrich.Items[] (constructed in the ESQL extract illustrated above) to the procedure correctly?
When I test I get the following error: "The procedure: 'ABC.GET_COMP_ID' with '3' parameters could not be match with a corresponding Database stored porocedure."
The documentation states this could be due to one of the following: The number of parameters are not the same or the types of parameters are not the same (i.e. IN/OUT/INOUT). I checked all this and do not see a problem.
Any help in this regard would be appreciated.
Regards
Andre
_________________
Regards
Andre
IBM Certified System Administrator -- WMQ V5.3
IBM Certified Solution Designer -- WBIMB V5 |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu May 11, 2006 2:27 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
From the manual (V6 manual, but applies to V5 as well):
| Quote: |
| User-defined types cannot be used as parameters or as return values. |
Your PACK_ID_ARRAY is a user defined type, which defines an array of the PACK_ID_REC user defined type, and is therefore not supported. There is no way to define an array of any type to be used as a stored procedure parameter.
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 |
|
|
| Ian |
| Posted: Thu May 11, 2006 2:39 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
What you are trying to do here is not supported as you are passing in an array as one of the parameters to the stored procedure call.
| Quote: |
| call SimplePackEnrich(O_status, O_error_msg, Environment.eaiEnrich.Items[]); |
In this case, the parameter Environment.eaiEnrich.Items[] will pass in the scalar value for Environment.eaiEnrich.Items and not the array of the tree below it.
In this case I suspect that this will result in NULL.
WBIMBv5 topic "ak04970_" does not explicitly state that an ARRAY parameter is not supported as WBIMBv5 does not have an ARRAY datatype.
This has been included the WMBv6 topic "ak04970_" following the introduction of the ESQL ROW and LIST datatypes.
_________________
Regards, Ian |
|
| Back to top |
|
|
| anvanzyl |
| Posted: Thu May 11, 2006 3:30 pm Post subject: |
|
|
Novice
Joined: 03 Jan 2005
Posts: 19
Location: Melbourne, Australia
|
Thanks for the responses guys. I suppose its back to the drawing board for me then. 
_________________
Regards
Andre
IBM Certified System Administrator -- WMQ V5.3
IBM Certified Solution Designer -- WBIMB V5 |
|
| 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
|
|
|
|
