MQSeries.net :: View topic - Stored Procedure returning NULL value from message flow

mainak

Hi,
I have written a Dynamic Stored Procedure which is running in a remote server and is fetching mutliple records from Customer Database. Now, the stored procedure in running perfectly. When I am testing from TOAD, I am getting the desired result. But, we I am calling the Stored Procedure from code written in ESQL deployed in MB 6.1, the stored procedure is returning NULL value in my message flow.

The code in my messsage flow where I am declaring and calling the stored procedure is:-

CREATE PROCEDURE GPSD_storedProcedure(IN databaseName CHARACTER,
IN MSISDN CHARACTER,
OUT wcud CHARACTER,
OUT voucherbarstatus INT,
OUT callbarstatus INT,
OUT installationdate DATE,
OUT firstcalldate DATE,
OUT activationdate DATE,
OUT deactivationdate DATE,
OUT deactivated DATE,
OUT balanceforfeiteddate DATE,
OUT balanceforfeitedat DATE,
OUT expirationdate DATE,
OUT deletedat DATE,
OUT substatus CHARACTER,
OUT amountstd INT,
OUT amountTT INT,
OUT novoucherbar INT,
OUT spday CHARACTER,
OUT fcc CHARACTER,
OUT hcm CHARACTER,
OUT msd CHARACTER,
OUT med CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME "SOAADM.GPSD_storedProcedure";

DECLARE wcud CHARACTER;
DECLARE voucherbarstatus INT;
DECLARE callbarstatus INT;
DECLARE installationdate DATE;
DECLARE firstcalldate DATE;
DECLARE activationdate DATE;
DECLARE deactivationdate DATE;
DECLARE deactivatedat DATE;
DECLARE balanceforfeiteddate DATE;
DECLARE balanceforfeitedat DATE;
DECLARE expirationdate DATE;
DECLARE deletedat DATE;
DECLARE substatus CHARACTER 'Hello';
DECLARE amountstd INT;
DECLARE amountTT INT;
DECLARE novoucherbar INT;
DECLARE spday CHARACTER;
DECLARE fcc CHARACTER;
DECLARE hcm CHARACTER;
DECLARE msd CHARACTER;
DECLARE med CHARACTER;

CALL GPSD_storedProcedure(Environment.Variable.DatabaseName,
Environment.Variable.MSISDN,
wcud,
voucherbarstatus,
callbarstatus,
installationdate,
firstcalldate,
activationdate,
deactivationdate,
deactivatedat,
balanceforfeiteddate,
balanceforfeitedat,
expirationdate,
deletedat,
substatus,
amountstd,
amountTT,
novoucherbar,
spday,
fcc,
hcm,
msd,
med);

Now, substatus should return is returning I and installationdate is returning 20-AUG-10 when I am calling from Toad. But from MB, it is returning NULL.

The declaration of the stored procedure which is working fine is:--

CREATE OR REPLACE PROCEDURE GPSD_storedProcedure(pDatabaseName_catalog IN VARCHAR2,
MSISDN IN VARCHAR2,
wcud OUT VARCHAR2,
voucherbarstatus OUT NUMBER,
callbarstatus OUT NUMBER,
installationdate OUT DATE,
firstcalldate OUT DATE,
activationdate OUT DATE,
deactivationdate OUT DATE,
deactivatedat OUT DATE,
balanceforfeiteddate OUT DATE,
balanceforfeitedat OUT DATE,
expirationdate OUT DATE,
deletedat OUT DATE,
substatus OUT VARCHAR2,
amountstd OUT NUMBER,
amountTT OUT NUMBER,
novoucherbar OUT NUMBER,
spday OUT VARCHAR2,
fcc OUT VARCHAR2,
hcm OUT VARCHAR2,
msd OUT VARCHAR2,
med OUT VARCHAR2)