stored Proc Returning only the Value 7

  • Thread starter Thread starter Sabby
  • Start date Start date
S

Sabby

i have written a stored procedure in Oracle with client
Oracle 8. This procedure is called by an ASP.NET
Application with Code written in C#

PROCEDURE Spvalidatemobilenumbernew(
pinumOldMobileNumber IN SERIALNUMBERTRACK.N_SERIALNUMBER%
TYPE,
pinumSimNumber IN SIMMOBILE.N_SIMNUMBER%TYPE,
pinumResult OUT NUMBER,
)


IS

countSerialnoTrack NUMBER;
countItemMaster NUMBER;
varStatus SERIALNUMBERTRACK.V_STATUS%TYPE;
charDeleted SIMMOBILE.B_DELETED%TYPE;
varCategory ITEMMASTER.V_ITEMCATEGORYTYPEID%TYPE;

BEGIN
SELECT COUNT(*) INTO countSerialnoTrack
FROM SERIALNUMBERTRACK
WHERE N_SERIALNUMBER =pinumOldMobileNumber AND
UPPER(V_SERIALNUMBERTYPE)='M';

IF( countSerialnoTrack > 0) THEN
--get the status of the mobile number if present
SELECT V_STATUS INTO varStatus FROM
SERIALNUMBERTRACK
WHERE N_SERIALNUMBER =pinumOldMobileNumber AND
UPPER(V_SERIALNUMBERTYPE)='M'AND
V_STOCKCODE='MB_00_000';

--chk the status of the mobile number and take respective -
--action

IF(UPPER(varStatus)='ASSIGNED') THEN
pinumResult :=1;
RETURN;
ELSE
IF(UPPER(varStatus)='UNBLOCKED') THEN
pinumResult :=2;
RETURN;
ELSE
IF(UPPER(varStatus)='OUTOFSYS') THEN

SELECT B_DELETED INTO charDeleted FROM SIMMOBILE WHERE
N_SIMNUMBER=pinumSimNumber AND
N_MOBILENUMBER= pinumOldMobileNumber;

IF(UPPER(charDeleted) = 'N') THEN

SELECT COUNT(*) INTO countItemMaster FROM ITEMMASTER
WHERE V_STOCKCODE='MB_00_000';

IF(countItemMaster >0) THEN
SELECT V_ITEMCATEGORYTYPEID INTO varCategory FROM
ITEMMASTER WHERE V_STOCKCODE='MB_00_000';


IF(UPPER(varCategory)='CRPTP') THEN

--success

pinumResult :=3;

RETURN;

ELSE


pinumResult :=4;


RETURN;

END IF;

ELSE


pinumResult :=5;


RETURN;

END IF; --countitemmaster

ELSE


pinumResult :=6;


RETURN;
END IF;
END IF; -- outofsys
END IF; --unblocked
END IF; --assigned

ELSE --old mobile no does not exist in
SERIALNUMBERTRACK


pinumResult :=7;
pivarvchanswer:='seven';
RETURN;
END IF;
END Spvalidatemobilenumbernew;

The issue is that irrespetive of whatever the value of
input parameteres is the result returned is always 7 . i
have validated the queries written in the procedure
individually for some input values. when executed
individually they retun a different value for an input.
when the same input is passed as parameter to procedure,
the procedure returns only 7.
I would appreciate any help or guidance in solving this
matter.
Thanx and regrads
Sabby
 
Sabby said:
i have written a stored procedure in Oracle with client
Oracle 8. This procedure is called by an ASP.NET
Application with Code written in C#
Click to expand...

Does it return so mething other than 7 when you run it from SQLPlus?


declare
result NUMBER;
begin
Spvalidatemobilenumbernew('123',123,result);
dbms_output.put_line(to_char(result));
end;


David
 
hi david thanx for the prompt reply. i found out the error
which i had committed. i was not sending the input
parameteres to the stored procedure in the right order.
Though i also checked the output the way u had said
thanx and regrads
Sabby
 
Back
Top