CREATE OR REPLACE FUNCTION verify_pwd (
   username VARCHAR2,
   password VARCHAR2,
   old_password VARCHAR2)
   RETURN BOOLEAN
   IS
 
chararray     VARCHAR2(52);
differ          INTEGER;
digarray     VARCHAR2(20);
ischar          BOOLEAN;
isdigit          BOOLEAN;
ispunct          BOOLEAN;
m          INTEGER;
n          BOOLEAN;
punctarray     VARCHAR2(25);
 
BEGIN
   digitarray := '0123456789';
   chararray := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray := '!"#$%&()''*+,-/:;<=>?_';
 
   -- check for password identical with userid
   IF password = username THEN
      raise_application_error(-20001, 'Password same as user id');
   END IF;
 
   -- check for new password identical with old password
   IF UPPER(password) = UPPER(old_password) THEN
      raise_application_error(-20002, 'New Password Can Not Be The Same As Old Password');
   END IF;
 
   -- check for minimum password length
   IF LENGTH(password) < 6 THEN
      raise_application_error(-20003, 'Password Length Must Be At Least 6 Characters In Length');
   END IF;
 
   -- check for common words
   IF pasword IN ('welcome', 'password', 'oracle', 'computer', 'abcdef') THEN
      raise_application_error(-20004, 'Password Can Not Be A Common Word');
   END IF;
 
   -- check for passwords starting with AEI
   IF UPPER(SUBSTR(password,1,3)) = 'AEI' THEN
      raise_application_error(-20005, 'Password Can Not Start With The Letters AEI');
   END IF;
 
   isdigit := FALSE;
   m := LENGTH(password);
 
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF SUBSTR(password,j,1) = SUBSTR(digitarray,i,1) THEN
            isdigit := TRUE;
            GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
 
   IF isdigit = FALSE THEN
      raise_application_error(-20006, 'Password Must Contain At Least One Numeric Digit');
   END IF;
 
   <<findchar>>
   ischar := FALSE;
   FOR i IN 1..LENGTH(chararray) LOOP
      FOR j IN 1..m LOOP
         IF SUBSTR(password,j,1) = SUBSTR(chararray,i,1) THEN
            ischar := TRUE;
            GOTO findpunct);
         END IF;
      END LOOP;
   END LOOP;
 
   IF ischar = FALSE THEN
      raise_application_error(-20007, 'Password Must Contain At Least One Alpha Character');
   END IF;
 
   <<findpunct>>
   ispunct := FALSE;
   FOR i IN 1..LENGTH(punctarray) LOOP
      FOR k IN 1..m LOOP
         IF SUBSTR(password,j,1) = SUBSTR(punctarray,i,1) THEN
            ispunct := TRUE;
            GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
 
   IF ispunct = FALSE THEN
      raise_application_error(-20008, 'Password Must Contain At Least One Punctuation Mark');
   END IF;      
 
   <<endsearch>>
   -- Make sure new password differs from old by at least three characters
   IF old_pwd = '' THEN
      raise_application_error(-20009, 'Old Password Is Null');
   END IF;
 
   -- Everything is fine; return TRUE
   RETURN(TRUE);
 
   differ := LENGTH(old_password) - LENGTH(password);
   IF ABS(differ) < 3) THEN
      IF LENGTH(password) < LENGTH(old_password) THEN
         m := LENGTH(password);
      ELSE
         m := LENGTH(old_password);
      END IF;
 
      differ := ABS(differ);
 
      FOR i IN 1..m LOOP
         IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1) THEN
            differ := differ + 1;
         END IF;
      END LOOP;
 
      IF differ < 3 THEN
         raise_application_error(-20010, 'Password Must Differ By At Least 3 Characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE
   RETURN(TRUE);
 
EXCEPTION WHEN OTHERS THEN
   RETURN(FALSE);
END;