Random Password Generator with a template

articles: 

Original blog post in here.

Give the password template to this function and get random password. It acts like dbms_random.string() function but takes more than one character as a parameter.

Here is a scenario (template)
- First char must be UPPERCASE
- Second and third one must be NUMBER
- Make fourth a NON-ALPHANUMERIC character
- Fifth one must be LOWERCASE
- Sixth is a NUMBER again
- Seventh is any character
- By the way, clients can't use all the non-alphanumerics, please choose them from easy's.

Usage :
select fRandomPass('unnelnr') from dual;

Result :
R91?k2y

Here is a function :

create or replace function fRandomPass (p_template in varchar2)
return varchar2 is
/*
Template characters :
* u upper case alpha characters only
* l lower case alpha characters only
* a alpha characters only (mixed case)
* x any alpha-numeric characters (upper)
* y any alpha-numeric characters (lower)
* n numeric characters*
* p any printable char (ASCII subset)*
* c any NON-alphanumeric characters
* b any non-alpha characters
* d any non-alpha characters (useful mode)
* e any NON-alphanumeric characters (useful mode)
*/
n number;
rVal number;
v varchar2(1);
vRet varchar2(4000);
vStr varchar2(4000);
nRng number;
begin
n := length(p_template);
v := '';
vRet := '';
for i in 1..n loop
v := substr(p_template,i,1);
if v = 'u' then -- upper case alpha characters only
vStr := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';
elsif v = 'l' then -- lower case alpha characters only
vStr := q'[abcdefghijklmnopqrstuvwxyz]';
elsif v = 'a' then -- alpha characters only (mixed case)
vStr := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]';
elsif v = 'x' then -- any alpha-numeric characters (upper)
vStr := q'[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ]';
elsif v = 'y' then -- any alpha-numeric characters (lower)
vStr := q'[0123456789abcdefghijklmnopqrstuvwxyz]';
elsif v = 'n' then -- numeric characters
vStr := q'[0123456789]';
elsif v = 'p' then -- any printable char (ASCII subset)
vStr := q'[ !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~]';
elsif v = 'r' then -- any printable char (ASCII subset) (useful mode)
vStr := q'[!#$%&()*0123456789+/<=>?@\{}[]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]';
elsif v = 'c' then -- any NON-alphanumeric characters
vStr := q'[ !"#$%&'()*+,-./:;<=>?@[\]^_`{|}~]';
elsif v = 'e' then -- any NON-alphanumeric characters (useful mode)
vStr := q'[!#$%&()*+/<=>?@\{}[]]';
elsif v = 'b' then -- any non-alpha characters
vStr := q'[ !"#$%&'()*+,-./0123456789:;<=>?@[\]^_`{|}~]';
elsif v = 'd' then -- any non-alpha characters (useful mode)
vStr := q'[!#$%&()*0123456789+/<=>?@\{}[]]';
else
vStr := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';
end if;
nRng := length(vStr);
rVal := TRUNC(nRng * dbms_random.value) + 1;
vRet := vRet || substr(vStr,rVal,1);
end loop;
return vRet;
end;