Generate Upper and Lowercase Alphanumeric Random String in Oracle

Question

How does one generate an upper and lowercase alphanumeric random string from oracle?

I have used select DBMS_RANDOM.STRING('x', 10) from dual to generate uppercase alphanumeric characters

and select DBMS_RANDOM.STRING('a', 10) from dual to generate uppercase and lowercase alpha characters

...but I'd like a function that does both upper and lower case, and alpha and numeric characters.

Also, bonus points (or just upvotes) if you can think of good reasons why Oracle didn't implement this?

Answer 1

You can make your own function. This is one option:

create or replace function random_str(v_length number) return varchar2 is
    my_str varchar2(4000);
begin
    for i in 1..v_length loop
        my_str := my_str || dbms_random.string(
            case when dbms_random.value(0, 1) < 0.5 then 'l' else 'x' end, 1);
    end loop;
    return my_str;
end;
/

select random_str(30) from dual;

RANDOM_STR(30)
--------------------------------------------------------------------------------
pAAHjlh49oZ2xuRqVatd0m1Pv8XuGs

You might want to adjust the 0.5 to take into account the different pool sizes - 26 for l vs. 36 for x. (.419354839?). You could also use value() and pass in the start and end range of the character values, but that would be character-set specific.

As to why... do Oracle need a reason? The use of x might suggest that it was originally hexadecimal and was expanded to include all upper-case, without it occurring to them to add a mixed-case version at the same time.

Answer 2

Try this,

with
  r as (
    select
      level lvl,
      substr(
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
        mod(abs(dbms_random.random), 62)+1, 1) a
    from dual connect by level <= 10
  )
select
  replace(sys_connect_by_path(a, '/'), '/') random_string
from r
where lvl = 1
start with lvl = 10
connect by lvl + 1 = prior lvl
;

Output,

FOps2k0Pcy