create or replace FUNCTION get_random_password(p_password_format IN VARCHAR2) RETURN VARCHAR2 IS
     format_len    NUMBER        := LENGTH(p_password_format);
        /****************************************
         * Template characters                  *
         ****************************************
         * U = upper case alpha characters only, strip off O and I (look the same in certain font as 0 and 1) *
         * L = lower case alpha characters only, strip off O and I (look the same in certain font as 0 and 1) *
         * A = mixed case alpha characters only, strip off O and I (look the same in certain font as 0 and 1) *
         * N = numeric characters              , strip off 0 and 1 (look the same in certain font as O and I) *
         * ? = NON-alpha characters     #_$    , limit to the ones that no require double quotes              *
         ****************************************/
     password_string VARCHAR2(132) := NULL;
     FUNCTION get_char(p_char IN VARCHAR2) RETURN VARCHAR2 IS
        FUNCTION get_random_char(p_string IN VARCHAR2) RETURN VARCHAR2 IS
        BEGIN
            RETURN(SUBSTR(p_string,DBMS_RANDOM.VALUE(0,LENGTH(p_string)),1));
        END get_random_char;
     BEGIN
        IF    (p_char = 'U') THEN RETURN(get_random_char('ABCDEFGHJKLMNPQRSTUVWXYZ'));
        ELSIF (p_char = 'L') THEN RETURN(get_random_char('abcdefghijklmnopqrstuvwxyz'));
        ELSIF (p_char = 'A') THEN RETURN(get_random_char('ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjklmnopqrstuvwxyz'));
        ELSIF (p_char = 'N') THEN RETURN(get_random_char('23456789'));
        ELSIF (p_char = '?') THEN RETURN(get_random_char('#{}[]\|=+-*<>:_$'));
        ELSE RETURN(NULL);
        END IF;
     END get_char;
  BEGIN
     password_string := NULL;
     -- check password_format
     IF (p_password_format IS NULL) THEN
        -- if no password_format is specified, we will return DUMMY as password
        RETURN('DUMMY');
     ELSE
        <<read_format_char>>
        FOR i IN 1..format_len LOOP
           password_string := password_string || get_char(SUBSTR(p_password_format,i,1));
        END LOOP read_format_char;
        RETURN(password_string);
     END IF;
  END get_random_password;
/