CREATE OR REPLACE PROCEDURE IVR_GET_TPIN1(DIGI_LINE_NO VARCHAR2,

PASSWORD OUT VARCHAR2,

STATUS OUT VARCHAR2) AS



x binary_integer;
pass dd_user.password%type;
sta dd_user.status%type;

BEGIN
SELECT PASSWORD,STATUS INTO pass,sta FROM DD_USER
WHERE USER_ID = DIGI_LINE_NO;

x :=DBMS_RANDOM.RANDOM;
x :=substr(x,1,4);
-- DBMS_OUTPUT.PUT_LINE(x);
if pass is null then

update dd_user set password = to_char(x),
password1 = to_char(x),
status = 'NEW' where USER_ID = digi_line_no;

else

update DD_USER set password1 = to_char(x),
status = 'CH', where USER_ID = digi_line_no;
end if;
end;
/


i tried above procedure,but could not get


exact out put,any one can modify the above one.

CONDITIONS INSIDE STORED procedure

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

if the passsword existed and in the form of alpha-numeric,
new random 4 digit password will be returned and update the database.

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



SQL> DESC DD_USER;
Name Null? Type
----------------------------------------- -------- ----------------

USER_ID NOT NULL VARCHAR2(20)
PASSWORD VARCHAR2(20)
PASSWORD1 NUMBER(4)
STATUS VARCHAR2(6)


This table stores the mobile information through web.
Now we have modified above table ,i mean we added new column

PASSWORD1 NUMBER(6)

I AM GIVING ALL DETAILS BELOW REGARDING PROCEDURE.