Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

data not showing when running stored procedure

833676Mar 4 2011 — edited Mar 4 2011
this 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 ;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2011
Added on Mar 4 2011
17 comments
351 views