| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Message Broker calling Oracle Stored Procedure |
« View previous topic :: View next topic » |
| Author |
Message
|
| FraggleRock |
 Posted: Thu Sep 21, 2006 12:00 am Post subject: Message Broker calling Oracle Stored Procedure |
 |
|
Newbie
Joined: 20 Sep 2006
Posts: 4
|
Hi I hope someone can help. I am trying to call an Oracle Stored Procedure, and I am struggling to understand how to create the ESQL for the Create Procedure section for the data type that are declare in the stored procedure as "IS TaBLE" % TYPE
i.e TYPE HandlingAgentKeyType IS TABLE OF handling_agent.handling_agent_key%TYPE
--ESQL
CREATE PROCEDURE get_handling_agents (IN p_user_id CHAR, INOUT nbr_rows INT, OUT err_num INT, OUT err_msg CHAR, OUT handling_agent_key_list ????, OUT handling_agent_list ????)
LANGUAGE DATABASE
EXTERNAL NAME "user_profiles.get_handling_agents_list"
END;
---Stored Procedure---
CREATE OR REPLACE
PACKAGE user_profiles AS
-- CURRENT VERSION: $Logfile: /Code/Build/Database/Build/stored_procs/user_profiles_hd.sql $ $Revision: 2 $
TYPE HandlingAgentKeyType IS TABLE OF handling_agent.handling_agent_key%TYPE
INDEX BY BINARY_INTEGER;
TYPE HandlingAgentType IS TABLE OF handling_agent.name%TYPE
INDEX BY BINARY_INTEGER;
FUNCTION get_handling_agent_list
( p_user_id IN VARCHAR2,
nbr_rows IN OUT BINARY_INTEGER,
err_num OUT INTEGER,
err_msg OUT VARCHAR2,
handling_agent_key_list OUT HandlingAgentKeyType,
handling_agent_list OUT HandlingAgentType) RETURN BOOLEAN;
PROCEDURE stop_handling_agents ;
PROCEDURE reinstate_user_profile( err_num IN OUT NUMBER,
err_msg IN OUT VARCHAR2,
p_user_profile_id IN user_detail.user_profile_id%TYPE );
END user_profiles; |
|
| Back to top |
|
 |
| FraggleRock |
| Posted: Fri Sep 29, 2006 6:23 am Post subject: The answer is DYNAMIC RESULT SETS and re-writing my procs |
|
|
Newbie
Joined: 20 Sep 2006
Posts: 4
|
The solution is return data sets in reference cursors which forces Oracle return the data in memory to Message Broker. This can then be accessed in Message Broker in code and manipulated if needed. The oracle stored procedure needs to be altered. Below is an example of the an stored procedure that returns an reference cursor and the message broker code used to call the stored procedure.
CREATE OR REPLACE PACKAGE AIBPROCEDURES IS
PROCEDURE SP_Get_Handling_Agents (
p_user_id IN VARCHAR2,
handlingAgents OUT SYS_REFCURSOR);
END AIBPROCEDURES;
/
CREATE OR REPLACE PACKAGE BODY AIBPROCEDURES IS
PROCEDURE SP_Get_Handling_Agents(
p_user_id IN VARCHAR2,
handlingAgents OUT SYS_REFCURSOR) IS
BEGIN
OPEN handlingAgents FOR SELECT handling_agent_key, name
FROM handling_Agent
ORDER BY handling_agent_key;
END SP_Get_Handling_Agents;
END AIBPROCEDURES;
This can be called from Message Broker using new version functionality of Dynamic Result Sets. The second option allows more then one array set to be returned to Message Broker.
Test the call using Dynamic Result Sets
Call get_handling_agents_list('testuserid',DB_IN_Ref,ErrCode, ErrMsg, Environment.Variables.AgentKeyList.[]);
Set OutputRoot.XML."esb:esbMessage"."esb:BHandlingAgentsGetSig"."cs:noRows1" = CARDINALITY(Environment.Variables.AgentKeyList[]);
CREATE PROCEDURE get_handling_agents_list (IN p_user_id CHAR,)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "AOMIS.AIBPROCEDURES.SPGetHandlingAgents"; |
|
| 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
|
|
|
|
