CREATE TABLE MOBUSER(
MOBNO VARCHAR2(20) PRIMARY KEY,
PASSWORD VARCHAR2(20),
STATUS VARCHAR2(1));

This table contains 1lack records.
here user passwords contain alpha numeric.
we like to change all old users passwords
and new users as 4 digit numeric password.
So,I need a stord procedure by check the old passwords when
they login and convert that one into numeric.


details of stored procedure
inputparameter mobno
outputparameter : password
: status


if the password not existed(new users) for a particuler user, new random 4 digit password
will be returned.

if the password existed and in the form of alpha-numeric, new random 4 digit

password will be returned.

if 4 digit password existed for that user, initial password will be returned.

MODIFIED TABLE

CREATE TABLE MOBUSER(
MOBNO VARCHAR2(20) PRIMARY KEY,
PASSWORD VARCHAR2(20),
STATUS VARCHAR2(1),
PASSWORD1 NUMBER(4));

here password and password1 are nulls for new users registration.

many thanks,i need this procedure soon,
any one can send me soon.
thanks
kavitha