Problem in Executing stored Procedure

From: sunila <s_beta_at_rediffmail.com>
Date: 22 Nov 2001 22:24:37 -0800
Message-ID: <8c250006.0111222224.523ed4fd@posting.google.com>

PROCEDURE CM_TEMP(
pDealType IN VARCHAR , --The Deal Type will be either 1 - Payable, 2 - Receivable
pSchemeCD IN NUMBER,
pAgentCD IN NUMBER, -- This will only be there if deal type is payable pInvName IN VARCHAR,
pAppldt IN DATE,
pInvAmt IN NUMBER,
pSplRate IN NUMBER,
pSplFltAmt IN NUMBER,
pPoints IN NUMBER,
pCBMCD IN NUMBER,
pCTMCD IN NUMBER,
pApplNo IN VARCHAR,
pCenterCD IN VARCHAR,-- This will only be there if deal type is payable

pTenure  IN NUMBER,
pPayTo  IN VARCHAR, -- This will only be there if deal type is payable
pPayAt  IN VARCHAR, -- This will only be there if deal type is payable

pUserID IN NUMBER,
pErrNo OUT NUMBER, -- In case Agent CD or Center Code or Duplicate Record or there an error has occured, it will be -1. If the procedure is successful, then it will be 1.
pErrDesc OUT VARCHAR2) -- In case Errno is -1, the errdesc will be there.
AS

BEGIN
pErrNo :=10;
pErrDesc :='Successful';
END; Problem not identified & solution not known.

With the above stored procedure it was found that the procedure is getting executed absolutely perfectly from SQL Plus, TOAD. But when it was tested through VB it gives “Illegal Operation Performed” error, through ASP it shut down all the services & web sites of IIS Server.

This stored procedure has 17 as the total no. of parameters. We tried it by reducing the parameters to 5 then it worked. We tried it by making 10 parameters again it worked. But the procedure is not working for all the parameters. Now the same procedure is run on different PC & it got executed properly. Again, to get the precise result we have deleted all the code from the procedure body.

Observation :
1. Stored Procedure works on all PC for less parameters (i.e. less than 17 parameters).

2. Worked only from 2 pc for all the parameters ( i.e. 17 parameters).
3. Worked perfectly from SQL Plus, TOAD.
4. Worked with different set of ODBC drivers installed on LOCAL PC.

Meaning, ODBC Drivers have nothing to do with the procedure.

If anyone can have suggestion in this regard pls send it to me SUNILA HEDAU Received on Fri Nov 23 2001 - 00:24:37 CST