| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| VERY URGENT!!! CALL ORACLE DB CREATE PROCEDURE (WMSI CSD03) |
« View previous topic :: View next topic » |
| Author |
Message
|
| danielaphex |
 Posted: Thu Apr 24, 2003 7:41 am Post subject: VERY URGENT!!! CALL ORACLE DB CREATE PROCEDURE (WMSI CSD03) |
 |
|
Apprentice
Joined: 07 Apr 2003
Posts: 28
|
Hi to everyone,
I am trying to call a Oracle Stored Procedure from a Compute Node and I am having some difficulties to retrieve ResultSets from the call to this external procedure.
We are using WMQSI CSD03 and the code I wrote looks like:
| Quote: |
DECLARE INPARM CHAR;
DECLARE OUTPARM CHAR;
SET INPARM = '1234';
-- Llamada al procedimiento almacenado en ESQL
CALL PROCEDIMIENTO (INPARM , OUTPARM);
--SET OutputRoot.XML.Mensaje.Daniel[] = Environment.Variables.Daniel[];
-- Definicion del procedimiento almacenado en ESQL
CREATE PROCEDURE PROCEDIMIENTO (IN ENTRADA CHARACTER , OUT SALIDA CHARACTER) EXTERNAL NAME "MQIDESA.SERVICIOS_EAI.S1002_B";
|
The procedure should answer a REF CURSOR variable but when I execute this flow I obtain the following error code:
| Quote: |
Apr 24 17:35:37 eaidesa WMQIv210[43604]: (EAIBKDESA1_BK.Grupo_DPG)[2082]BIP2230E
: Error detected whilst processing a message in node 'LLAMADA PROCEDIMEINTO ALMA
CENADO.LLAMAR A PROCEDIMIENTO ALMACENADO'. : EAIBKDESA1_BK.114f8f4c-f200-0000-00
80-cfb54220f4dc: /build/S210_P/src/DataFlowEngine/ImbComputeNode.cpp: 453: ImbCo
mputeNode::evaluate: ComIbmComputeNode: 1159b067-f400-0000-0080-b21539b2db58
Apr 24 17:35:37 eaidesa WMQIv210[43604]: (EAIBKDESA1_BK.Grupo_DPG)[2082]BIP2921E
: The procedure 'MQIDESA.SERVICIOS_EAI.S1002_B' with '2' parameters could not be
matched with a corresponding Database stored porocedure. : EAIBKDESA1_BK.114f8f
4c-f200-0000-0080-cfb54220f4dc: /build/S210_P/src/DataFlowEngine/ImbRdl/ImbRdlRo
utine.cpp: 680: SqlRoutine::resolveExternalParameters: ComIbmComputeNode: 1159b0
67-f400-0000-0080-b21539b2db58
|
althougth the external procedure do receive two parameters.
The definition of the procedure is the following:
| Quote: |
CREATE OR REPLACE PACKAGE SERVICIOS_EAI AS
-- Package constant
-- types declaration
TYPE t_Contrato IS RECORD (
NOMBRE VARCHAR2(15),
APELLIDO1 VARCHAR2(30),
APELLIDO2 VARCHAR2(30),
DIRECCION VARCHAR2(50));
TYPE c_Contratos IS REF CURSOR RETURN t_Contrato;
TYPE c_Contratos IS REF CURSOR RETURN HR006_SISTEMA%ROWTYPE;
-- Package PROCEDURE
PROCEDURE S1002_B ( pNUM_TFNO IN VARCHAR2,
pCONTRATOS OUT SERVICIOS_EAI.c_Contratos);
END;
/
CREATE OR REPLACE PACKAGE BODY SERVICIOS_EAI AS
-- ******************************************************
-- SearchLocationBuilding Procedure
-- ******************************************************
PROCEDURE S1002_B ( pNUM_TFNO IN VARCHAR2,
pCONTRATOS OUT SERVICIOS_EAI.c_Contratos)
AS
Resultado NUMBER;
NumError NUMBER;
TYPE tContratos IS REF CURSOR;
v_Contratos tContratos;
BEGIN
Resultado := 0;
OPEN pCONTRATOS FOR SELECT * FROM HR006_SISTEMA;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
|
Could anybody please help??? |
|
| Back to top |
|
 |
| lillo |
| Posted: Thu Apr 24, 2003 11:40 pm Post subject: |
|
|
Master
Joined: 11 Sep 2001
Posts: 224
|
Daniel,
I was trying to do something similar a few months ago and I couldn´t return the refcursor.
The following is from the ESQL manual for CSD2. I haven´t had a chance to get this manual for CSD3, but I don´t have any notice about a change on this.
| Quote: |
All external procedures have the following restrictions:
Parameters cannot be of the ESQL Reference type.
Parameters cannot map onto the database LOB types (for example, BLOB and CLOB).
Results sets cannot be returned or used as input or output parameters.
|
Tell us if you finally get it to work because we are very interested on this.
_________________
Lillo
IBM Certified Specialist - WebSphere MQ |
|
| 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
|
|
|
|
