data not showing when running stored procedure
833676Mar 4 2011 — edited Mar 4 2011this is my stored procedure is it compiled well with no problem but when run this it is not returning me any data
or rows i give my procedure code also
here i am calling it
declare
cv_1 SYS_REFCURSOR;
begin
SP_ISVALIDLOGIN ('SuperAdmin','1',1,'HIS-SAGIR',cv_1);
end;
procedure code
create or replace
PROCEDURE sp_IsValidLogIn
(
p_UserName NChar ,
p_Password NvarChar2,
p_Remember INT,
p_MachineName NVARCHAR2,
cv_1 IN OUT SYS_REFCURSOR
)
AS
v_MachineName1 varchar(50) ;
v_LoginStatus char(5):='';
v_cnt number(10,0):=0;
EX_InvalidUserName Exception;
EX_AlreadyLogged Exception;
begin
SELECT count(*) into v_cnt FROM User_Mst WHERE UPPER(UserName) = UPPER(p_UserName);
IF v_cnt=0 then
raise EX_InvalidUserName ;
END if;
select NVL(Logged,0) into v_LoginStatus FROM User_Mst WHERE UPPER(UserName) = UPPER(p_UserName);
if v_LoginStatus = 1 then
select NVL(machinename,'') into v_MachineName1 from user_mst
WHERE UPPER(UserName) = UPPER(p_UserName);
raise EX_AlreadyLogged;
end if;
open cv_1 for
SELECT UserID,Password, NVL(PassExpDate,sysdate) as PassExpDate
FROM User_Mst WHERE UPPER(UserName) = UPPER(p_UserName);
--- AND (Password) = (p_Password)
exception when EX_InvalidUserName then
raise_application_error( -20002, 'Invalid UserName.' );
-- dbms_output.put_line('invalid');
when EX_AlreadyLogged then
raise_application_error( -20002, 'User is already logged in on Machine :' );
END ;