Pronounceable password generator in PL/SQL

Published by Richard Martens on

function auth_pwgen return varchar2 is
  l_pos integer;
  l_pw  varchar2(100);
  l_c   varchar2(100) := 'bcdfghjklmnprstvwz'; --consonants
  l_v   varchar2(100) := 'aeiou'; --vowels
  l_a   varchar2(100) := l_c || l_v; --both
  l_s   varchar2(100) := '!@#$%^&*()';
  --use three syllables...
  for ii in 1 .. 3
    l_pw := l_pw || substr(l_c, trunc(dbms_random.value(0, length(l_c))), 1);
    l_pw := l_pw || substr(l_v, trunc(dbms_random.value(0, length(l_v))), 1);
    l_pw := l_pw || substr(l_a, trunc(dbms_random.value(0, length(l_a))), 1);
  end loop;
  --... Make one Uppercase
  l_pos := round(dbms_random.value(1, length(l_pw)));
  l_pw  := substr(l_pw, 1, l_pos - 1) 
        || upper(substr(l_pw, l_pos, 1)) 
        || substr(l_pw, l_pos + 1);
  --... and add a nice number
  l_pw := l_pw || round(dbms_random.value(10, 99));
  --... and add a special character
  l_pw := l_pw || substr(l_s, dbms_random.value(1, length(l_s)), 1);
  return l_pw;
end auth_pwgen;
Categories: APEXPL/SQL

Richard Martens

Richard Martens has been involved in information technology for more than 20 years. He started as a web developer using the Oracle database as no more than data storage. Richard has been responsible for major European multilingual websites and has been working with the Oracle database since 2000. During those years, he developed himself using a multitude of technologies and specialized in PL/SQL and Oracle APEX. With APEX, he combines the things he loves most: the Oracle database and web technologies.