Tuesday, August 16, 2016

PL/SQL to generate passwords

set serveroutput on
DECLARE
   TYPE tCharList IS VARRAY(26) OF CHAR(1);
   TYPE tNumberList IS VARRAY(10) OF CHAR(1);
   vCharLower tCharList;
   vCharUpper tCharList;
   vCharNumber tNumberList;
   vRandomNumber INTEGER;
   vRandomList INTEGER;
   vPassword VARCHAR2(16);
   --vHowMany INTEGER;
BEGIN

   --vHowMany := 6;
   vCharLower := tCharList('a', 'b', 'c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');
   vCharUpper := tCharList('A', 'B', 'C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
   vCharNumber := tNumberList('0','1','2','3','4','5','6','7','8','9');
   --FOR n in 1 .. vHowMany LOOP
 
   SELECT dbms_random.value(1,26)
   INTO vRandomNumber
   FROM dual;
           
 

For j in 1 .. 10 LOOP
    vPassword := null;
     vPassword := vCharLower(vRandomNumber);      
   FOR i in 1 .. 15 LOOP
      SELECT dbms_random.value(1,3)
      INTO vRandomList
      FROM dual;
       
      CASE vRandomList
      WHEN 1 THEN
        SELECT dbms_random.value(1,26)
        INTO vRandomNumber
        FROM dual;
           
        vPassword := vPassword || vCharLower(vRandomNumber);
      WHEN 2 THEN
        SELECT dbms_random.value(1,26)
        INTO vRandomNumber
        FROM dual;
           
        vPassword := vPassword || vCharUpper(vRandomNumber);
       
      WHEN 3 THEN
     
        SELECT dbms_random.value(1,10)
        INTO vRandomNumber
        FROM dual;
       
        vPassword := vPassword || vCharNumber(vRandomNumber);
       
      END CASE;
   END LOOP;
   
   dbms_output.put_line(vPassword);
END LOOP;
END;
/