| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Getting an array from Oracle |
« View previous topic :: View next topic » |
| Author |
Message
|
| kevinobyrne |
 Posted: Wed Jul 03, 2013 8:55 am Post subject: Getting an array from Oracle |
 |
|
Voyager
Joined: 17 Jul 2007
Posts: 83
Location: Ireland
|
Using WMB 7.0.0.5 and Oracle 11.2
Is it possible to call a stored procedure that returns an array? For example, it would return a ROW/LIST into your flow?
e.g.
| Code: |
CREATE PROCEDURE FnGetPaymentIds( IN BatchId INTEGER)
RETURNS INTEGER LANGUAGE DATABASE EXTERNAL NAME "SCH.PKGMW_INTERFACE.fn_get_payment_ids"; |
Then calling it with
| Code: |
DECLARE Payments ROW;
CALL FnGetPaymentIds(1) INTO Payments; |
I'm getting "optional feature not implemented" with this. I also tried to get the procedure to return a ROW type but that wouldn't deploy.
Would I be better off just getting the DB procedure to do the work, returning something like a list of comma separated values in a CLOB?
Thanks for any suggestions. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jul 03, 2013 8:57 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jul 03, 2013 9:38 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
Guess there is a option to return array as result set in Oracle 
Or it may be the other way round  , not very sure, you can have a try.
If this is possible, you can use dynamic result sets. |
|
| Back to top |
|
|
| kevinobyrne |
| Posted: Thu Jul 04, 2013 2:30 am Post subject: |
|
|
Voyager
Joined: 17 Jul 2007
Posts: 83
Location: Ireland
|
Thanks, result sets worked for me:
| Code: |
PROCEDURE sp_get_payment_ids (pBatchId IN NUMBER,
pCursor OUT sys_refcursor)
AS
tabPaymentMessages payment_messages_tab;
BEGIN
OPEN pCursor FOR
SELECT payment_id
BULK COLLECT INTO tabPaymentMessages
FROM batch_messages
WHERE batch_id = pBatchId;
END sp_get_payment_ids; |
Then my esql was:
| Code: |
CREATE COMPUTE MODULE MF_TEST_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
DECLARE BatchId INTEGER CAST(InputRoot.XMLNSC.Msg.BatchId AS INTEGER);
DECLARE Batch ROW;
CALL SpGetPaymentIds(BatchId, Batch.Payment[]);
SET OutputRoot.XMLNSC.Batch = Batch;
RETURN TRUE;
END;
END MODULE;
CREATE PROCEDURE SpGetPaymentIds ( IN pBatchId INTEGER)
LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "SCH.PKGMW_INTERFACE.sp_get_payment_ids";
|
|
|
| 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
|
|
|
|
