Quite a few changes in Shestakov 's post will get you the required.


create or replace procedure pwd_change(p_MOBNO varchar2 in, mob_pwd out,mob_status out )
as
rm MOBUSER%rowtype;
n number(20);

begin

begin
select * into rm from MOBUSER where mobno = p_mobno;
exception when others then
-- some error handling
return false;
end;

if rm.password is null then
--
-- work with new user
-- (n new digit password)
--
n := dbms_utility.get_hash_value( p_MOBNO || to_char(sysdate), 8999, power(2,30)) + 1000;

/*both the password fields for the newuser will be filled with the new password*/
update MOBUSER set password = substr(to_char(n),1,4)
password1 = substr(to_char(n),1,4) ,
status = 'NEW' where mobno = p_mobno;

mob_pwd:= substr(to_char(n),1,4) ;
mob_status:='NEW';

/* i do know what exactly the status is if required change it*/

else

if length(rtrim(rm.password))>4 then

n := dbms_utility.get_hash_value( rm.password || to_char(sysdate), 8999, power(2,30)) + 1000;

update MOBUSER set password1 = substr(to_char(n),1,4)
status = 'CHANGE' where mobno = p_mobno;
/*if you want update the same new password for firstpassword field also or else the old password will be maintained inthe first password field*/

set mob_pwd: =substr(to_char(n),1,4) ;
set status='CHANGE';

end if;
if length(rtrim(rm.password))=4 then


set mob_pwd: =rm.password;/*same password is returned*/
set status='NoCHANGE';

end if

end if

end;