If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

code to check the password at least one upper case letter

Hi,
I receive a task to setup so it can check the complexity of the password. I use utlpwdmg.sql to modify to meet the requirement at our site. The script does not check the password to have at least one upper case letter. I googled it and so far it said that oracle does not have the capability of checking the case sensitive until 11g patch. Our site database is 10g. Is there workaround or suggestion of what I can do or look. I truthly appreciate it.

m := length(password);
-- 3. Check for the punctuation
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO findupper;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20005, 'Password should contain at least one punctuation');
END IF;
-- 4. Check for at least one upper case letter
<>
ischar:=FALSE;
FOR i IN 1..length(upperarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(upperarray,i,1) THEN
ischar:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20006, 'Password must contain at least one upper case letter.');
END IF;
<>
-- if everything is fine retrun true.
RETURN(true);
END;
/

Hi LkBrwn,
I used the utlpwd.sql file and modified the way that it fit with my worksite requirement. I wish that the requirement match with what in the sql file. It requires to have at least one upper and one lower case letter. Some of the code I found on google did not work.

Also, take into consideration regexp_instr doesn't count how many "password" characters are in "regular_exp" but returns the position of the first "password" character that matches any "regular_expr" character so, for a password like AmyPaSsWord it would return "1"

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Hi PAVB,
I put in the the single quote around the password (maroon font below), ran teh function again, then tested out. The outcome no matter what I put in all lower case or one upper case, it error me out. The error below.

Hi Hrishy,
I will try your way to see if it work.

Hi PAVB and Hrishy,
I truthly to thank you so much for making effort and providing me recommendation of the code to test out.