Function to generate random password in PLSQL

The Oracle DBMS_RANDOM.STRING functionality does not allow you to state you want special characters expressly. Also, when you use DBMS_RANDOM.STRING for each type of character, you end up with a series that always has the same structure (which might even be a security issue again).

But if you do not want to have the same structure all the time, you would have to write something that would (in yet another random way) distribute the characters of your structured password.

Another option is to use an example I adopted from an AskTom entry; you leverage the existing functionality and try until you have a result:

create or replace FUNCTION GENPWD( 
  p_numbers      IN NUMBER, 
  p_specialchar  IN NUMBER, 
  p_lowercase    IN NUMBER, 
  p_uppercase    IN NUMBER) return varchar2 
IS
  v_length         number := p_numbers + p_specialchar + p_lowercase + p_uppercase;
  v_password       varchar2(200);
  v_iterations     number := 0;
  v_max_iterations number := 500;
BEGIN
    loop
        v_password := dbms_random.string('p',v_length);
        v_iterations := v_iterations + 1;

           exit when (regexp_count(v_password,'[a-z]') = p_lowercase
                 and  regexp_count(v_password,'[A-Z]') = p_uppercase
                 and  regexp_count(v_password,'[0-9]') = p_numbers) 
                 or v_iterations=v_max_iterations;

    end loop;

    if v_iterations = v_max_iterations THEN
      v_password := '';
    end if;

    return(v_password);
END;