| Author |
Message
|
| keenlearner |
 Posted: Tue Jun 26, 2007 11:47 pm Post subject: Multiple Variable Value return from Oracle Stored Procedure |
 |
|
Acolyte
Joined: 24 Aug 2006
Posts: 62
|
Hi Guyz
Envrionment : WinXP Service Pack 2
Message Broker 6.0
MQSeries 6.0
Database : Oracle 9i on Unix
ODBC : MQSeries DataDirect 5.0 32 Bit Oracle
I am calling an Oracle Stored Procedure from esql code which returns multiple out parameters.
The problem which i am facing is all the output parametes have the same value.
For example if there are two output parameters out1 and out2. the value of out1 and out 2 are same.
Though procedure is running fine when executed in Oracle Plus. |
|
| Back to top |
|
 |
| marcin.kasinski |
| Posted: Tue Jun 26, 2007 11:54 pm Post subject: Re: Multiple Variable Value return from Oracle Stored Proced |
|
|
Sentinel
Joined: 21 Dec 2004
Posts: 850
Location: Poland / Warsaw
|
Hi,
Can you show ESQL code and sample output from Oracle Plus and ESQL?
_________________
Marcin |
|
| Back to top |
|
|
| keenlearner |
| Posted: Wed Jun 27, 2007 12:39 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2006
Posts: 62
|
Hi Marcin
the ESQL CODE
| Code: |
BROKER SCHEMA BROKER_SCHEMA
CREATE PROCEDURE CALL_TEST_PROC_CROSS_REF( IN in_var1 CHARACTER,
INOUT out_var1 CHARACTER,
INOUT out_var2 CHARACTER)
LANGUAGE DATABASE
EXTERNAL NAME "XXINT.Evnironment_Population_PRC";
CREATE COMPUTE MODULE MulitpleValue_Return_MF_Multiple_Values
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
DECLARE in_var1 CHARACTER;
DECLARE out_var1 CHARACTER '';
DECLARE out_var2 CHARACTER '';
SET in_var1='test';
CALL CALL_TEST_PROC_CROSS_REF(in_var1, out_var1,out_var2);
SET OutputRoot.XML.Results.Value1 = out_var1;
SET OutputRoot.XML.Results.Value2 = out_var2;
RETURN TRUE;
END;
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
END MODULE;
|
Sample Out put From Oracle will be
out_var1=First Param
out_var2=Success;
But from Esql i m getting
out_var1=First Param
out_var2=First Param |
|
| Back to top |
|
|
| marcin.kasinski |
| Posted: Wed Jun 27, 2007 1:52 am Post subject: |
|
|
Sentinel
Joined: 21 Dec 2004
Posts: 850
Location: Poland / Warsaw
|
Do you use "test" as input parameter in both Oracle Plus and ESQL ?
Are you 100% sure that database output in both examples is "First Param" and "Success". Can you debug database ?
_________________
Marcin |
|
| Back to top |
|
|
| keenlearner |
| Posted: Wed Jun 27, 2007 2:41 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2006
Posts: 62
|
Hi Marcin
i am test the procedure from TOAD. You can get the results from there as dbms output put line .... It shows the values of out_var1 as First Param and second as Success,
Bur ESQK is not showing it/ |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Jun 27, 2007 3:21 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Are you using MB 6.0.0.0 or 6.0.0.3?
What is the SQL definition of the procedure in Oracle?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| keenlearner |
| Posted: Wed Jun 27, 2007 4:18 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2006
Posts: 62
|
jeff,
I am using Boker version 6.0.0.0
| Code: |
CREATE OR REPLACE PROCEDURE XXINT.Evnironment_Population_PRC (
in_var1 IN VARCHAR2,
out_var1 OUT VARCHAR2,
out_var2 OUT VARCHAR2) AS
BEGIN
out_var1 := 'First Param';
out_var2 := 'Success';
END Evnironment_Population_PRC;
|
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Jun 27, 2007 4:20 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You should apply FP3 or FP4 to go to 6.0.0.3 or 6.0.0.4.
It might help. It's also just the right thing to do.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
