dbawiki

with function rndstr(p_len in number) return varchar2 is
    l_random_string varchar(100);
begin
    -- start with a string of between 1 and 9 characters that starts with an alpha
    select dbms_random.string('a',substr(abs(dbms_random.random),1,1))
    into   l_random_string
    from   dual;
    -- add in a 'safe' non-alphanumeric and more alphanumerics up to the length required
    l_random_string := l_random_string||'#'||dbms_random.string('x',p_len-(length(l_random_string)+1));
    return l_random_string;
end;
select rndstr(14) from dual
/

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('123456789'));
        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;
/