Standard Password Validation SQL Block

Standard Password Validation SQL Block

Standard Password Format Like 

                       * One uppercase letter

                       * One lowercase letter

                       * One digit 

                       * One special character 

DECLARE

V_USERNAME    VARCHAR2(100);

V_OLD_PASSWORD VARCHAR2(60);

PASSWORD  VARCHAR2(60);

chars integer := 8;

letter integer := 1;

lower integer := 0;

upper integer := 1;

digit integer := 1;

special integer := 1;

digit_array varchar2(10) := '0123456789';

alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';

cnt_letter integer := 0;

cnt_upper integer := 0;

cnt_lower integer := 0;

cnt_digit integer := 0;

cnt_special integer := 0;

len integer := 0;

delimiter boolean := FALSE;

ch varchar2(100);

pw_lower varchar2(256);

BEGIN

V_USERNAME    := :P9991_USER_NAME;

V_OLD_PASSWORD := :P9991_OLD_PASSWORD;

PASSWORD := :P9991_NEW_PASSWORD;

len  := NVL (length(password), 0);

pw_lower := NLS_LOWER(password);

FOR i in 1..len LOOP

      ch := substr(password, i, 1);

      IF ch = '"' THEN

         delimiter := TRUE;

      ELSIF instr(digit_array, ch) > 0 THEN

         cnt_digit := cnt_digit + 1;

      ELSIF instr(alpha_array, NLS_LOWER(ch)) > 0 THEN

         cnt_letter := cnt_letter + 1;

         IF ch = NLS_LOWER(ch) THEN

            cnt_lower := cnt_lower + 1;

         ELSE

            cnt_upper := cnt_upper + 1;

         END IF;

      ELSE

         cnt_special := cnt_special + 1;

      END IF;

   END LOOP;

IF len>20 THEN

RETURN 'Password Must be less than 20 Characters';

ELSE 

IF V_OLD_PASSWORD = PASSWORD THEN

RETURN 'New password cannot be same as old Password' ;

ELSE 

        IF delimiter = TRUE THEN

        RETURN ('password must NOT contain a '|| 'double-quote character, which is '|| 'reserved as a password delimiter');

ELSE 

IF chars IS NOT NULL AND len < chars THEN

RETURN( 'Password length less than ' ||chars);

ELSE 

IF letter IS NOT NULL AND cnt_letter < letter THEN

RETURN( 'Password must contain at least ' ||letter || ' letter(s)');

ELSE 

IF upper IS NOT NULL AND cnt_upper < upper THEN

RETURN('Password must contain at least ' ||upper || ' uppercase character(s)');

ELSE 

IF lower IS NOT NULL AND cnt_lower < lower THEN

RETURN('Password must contain at least ' ||lower || ' lowercase character(s)');

ELSE

IF digit IS NOT NULL AND cnt_digit < digit THEN

RETURN('Password must contain at least ' ||digit || ' digit(s)');

ELSE

IF special IS NOT NULL AND cnt_special < special THEN

RETURN('Password must contain at least ' ||special || ' special character(s)');

ELSE

IF instr(pw_lower, NLS_LOWER(V_USERNAME)) > 0 THEN

RETURN('Password contains the username');

                                        ELSE 

RETURN NULL;

                                        END IF;

END IF;

END IF;

END IF;

END IF;

END IF;

END IF;

        END IF;

END IF;

END IF;

END;