Friday, June 3, 2016

Write a Pl/sql program to check the given username & password are correct. Display the meaningful messages

create table user_info(uname varchar2(20),
upin varchar2(20));
insert into user_info values('sridhar','sri123');
insert into user_info values('anil','anil123');
insert into user_info values('vivek','vikki123');
insert into user_info values('mahesh','mahi123');

--*Pl/sql Block checks for valid user name & password :
DECLARE
   puser        VARCHAR2 (20) := '&username';
   ppass        VARCHAR2 (20) := '&password';

   TYPE urec IS RECORD (
      u   VARCHAR2 (20),
      p   VARCHAR2 (20)
   );

   TYPE utab IS TABLE OF urec
      INDEX BY BINARY_INTEGER;

   ctr          NUMBER (2)    := 1;
   a            BOOLEAN       := FALSE;
   ut           utab;
   valid_user   EXCEPTION;
BEGIN
   FOR i IN (SELECT uname,
                    upin
               FROM user_info)
   LOOP
      ut (ctr).u       := i.uname;
      ut (ctr).p       := i.upin;
      ctr              := ctr + 1;
   END LOOP;

-- checking for user and password
   FOR k IN 1 .. ut.COUNT
   LOOP
      IF ut (k).u = puser AND ut (k).p = ppass
      THEN
         RAISE valid_user;
      ELSE
         a                := TRUE;
      END IF;
   END LOOP;

   IF a = TRUE
   THEN
      DBMS_OUTPUT.put_line (' Invalid user and password ');
   END IF;
EXCEPTION
   WHEN valid_user
   THEN
      DBMS_OUTPUT.put_line (' Login sucessful ');
END;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect